Note: ‘Technical Tidbits’ is an addition to this blog comprised of mini-blogs on different technical topics. Not all of these topics will be on the introduction level; in fact, most will deal with new things I’m learning as I continue on my own technical journey. These little blogs will expose you to advanced topics. You will want to read them and tuck them away for later review. For example, you may not have a clue about parameter tables now; but when you begin your study of the DAX language, you will remember the ‘tidbit’ blog and have it to review. Think of ‘Technical Tidbits’ as little markers down the road; they’re showing you what lies ahead.
DAX Master Wanna-Be
I’m on a quest to be a DAX Master. As Yoda of Star Wars fame wielded the ‘force’ as a Jedi warrior, I’d like to wield DAX functions to make Tabular Services and Excel Power BI slice through complex evaluation contexts! OK, I want to be taller and cuter than Yoda as well; I’m a stickler for detail.
So what is DAX and what is so great about DAX? Data Analysis Expressions is a formula language, used primarily with Tabular Services and Excel Power Pivot data models. Using DAX, you can create calculated columns and calculated fields on your data models; but our focus in ‘Technical Tidbit’ today is on creating a Parameter Table.
OK, what do DAX and Parameter Tables have to do with each other? What is a Parameter Table and what is the value of creating one? Well, you might not get as excited about Parameter Tables as I did, but I thought they were amazing when I first learned about them. As I read Ferrari and Russo’s book Microsoft Excel 2013: Building Data Models with PowerPivot, Chapter 7 dealt with ‘Understanding Evaluation Contexts’. Then, in order to bring Evaluation Context into the practical realm, they set up a real-world scenario utilizing the theory of evaluation context and introduced Parameter Tables.
I won’t go into the concept of ‘evaluation context’, but remember that it is VERY important to understanding DAX; using it wisely with data models and filtering is a must. With that said, let’s get to what a parameter table is and when they might be useful. A parameter table is when you create a little Excel table BUT it is unrelated to the rest of the data model. Well that doesn’t do anybody any good; but wait! This unrelated parameter table is used internally by DAX functions to modify the behavior of the other tables.
Say, for instance, you have a database with millions, billions or trillions of rows of Sales Amounts; trying to build a report on the sales column could drive you nuts. Now say your manager’s boss wants to see a report on all these sales. This amount of data would take up lots of space in a report and would not lend itself to concise reporting or easy analysis. We need a plan; a parameter table!
How to Create a Parameter Table
Your parameter table (let’s call it ‘ParameterSlicer) will allow the end user to decide the scale they want to view the sales amounts; for example: ‘Real Value’, ‘Thousands’, ‘Millions’, ‘Billions’, etc. The use of the parameter table does not filter the data; but allows you to change the scale used in viewing the numbers. Now that’s slick!
First, I used the ContosoRetailDW to create my data model, especially because there are millions of rows with lots of sales amount that will produce large sums. Then I created a little Excel table, named ‘ParameterSlicer’; giving it three columns ‘ShowValueAs’, ‘DivideBy’, and ‘Order’ along with four rows: ‘RealValue’, ‘Thousands’, ‘Millions’, ‘Billions’.
After you create the new table, you used the Table Import Wizard to load the Excel table to your data model. Remember, the new table is not related to anything inside your model; but once the table is in the model, you can use it as the source for a report slicer. This little Excel table will be the source for the slicer in that report your boss wants to see; but nothing will happen without the DAX Magic!
DAX
Without DAX, your little Excel table will sit in the data model and just look cute; lonely and unrelated, but cute. 
But that’s not good enough; we must use DAX code to make everything work. Your DAX formula must ‘see’ what the end user has selected in ‘ShowValueAs’ and modify the content of the report to display the values selected. I modified the DAX calculated field as shown in Ferrari and Russo’s book, and will try to explain it as simply as I can.
SumOfSalesAmount (this is just the name of your calculated field that was placed on the ‘Sales’ table) =
IF (
HASONEVALUE = ( ParameterSlicer[ShowValueAs]), (this is the table name|table column of parameter table)
SUM (Sales[SalesAmount]) / VALUE (ParameterSlicer [DivideBy]),
SUM(Sale[SalesAmount])
)
DAX Explained
The IF function works with another function HASONEVALUE. The IF function test a condition; if the end user does not select anything in the slicer, the HASONEVALUE will return FALSE and nothing happens. When the end user selects a single value on the Slicer table, then the HASONEVALUE will return TRUE and the sales numbers will be seen by the scale selected by the end user. It’s like saying “if the user has selected a single value in the slicer, then show the ‘sum of sales amount’ divided by the corresponding denominator; otherwise, show the total of the ‘sales amount”.
It’s important to remember that the ‘ParameterSlicer’ Excel table DOES NOT filter the data, it will simply allow the end user to change the scale of the numbers. Now with the Excel table loaded into the data model and this DAX calculated measured placed on your ‘Sales’ table, you can bring everything together. 
This is a simple example but the most common use for a parameter table; however, they can be used for much more. With a parameter table you can modify the way a number is computed, change parameters for an algorithm, or change values returned from a calculated field. So don’t dismiss this little guy.
Final Thoughts
The table names and columns have been changed to protect the innocent, just kidding; you know you’ll need to plug in your actual database table names and columns, but you get the idea of using parameter tables. Be sure to get Alberto Ferrari and Marco Russo’s excellent book: Microsoft 2013: Building Data Models with PowerPivot. The book is a great way to begin your journey into DAX. Until next time!
Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!