In Power BI, you can create three different types of parameters of what I call; Measure, Dimension and Switch parameters. They are similar but each of them has their own differences and use cases.
Note: If you are looking to read about user input parameters, see my other article here: https://www.antaresanalytics.net/post/2018/09/11/power-bi-creating-user-input-variables
Measure Parameters & Dimension Parameters
Both of these parameters are created with Field Parameters. This is located in Model -> New Parameter -> Fields.
Personally, I would like Power BI to distinguish the two, because it can get confusing if you are not aware of the difference. You see, when you create a Field Parameter, Power BI gives you the option to add measures and dimensional fields to the same parameter. This just doesn't really make sense, and indeed, if you try to use this parameter it will most likely not work (though there could be very specific situations in which you would want this behavior).
How to Create Measure Parameters
These are built using fields that are only measures. Measures parameters allow you (and your users) to easily select the measure they want to see in the visual. This means, you only select measures when choosing the fields from the parameter builder window.
You typically want to place measure parameters in the value or y-axis fields in your visuals. When used with a slicer, this allows the visual to dynamically change the measure being displayed based on the selected (measure) parameter value.
How to Create Dimension Parameters
These are built using fields that are only dimensions (columns from your tables). Dimension parameters allow you (and your users) to easily select the dimension they want to see in the visual. This means, you only select dimensions when choosing the fields from the parameter builder window.
You typically want to place dimension parameters in the rows or x-axis fields in your visuals. When used with a slicer, this allows the visual to dynamically change the dimension used based on the selected (dimension) parameter value.
Field Parameter DAX Code
(Measure) Parameter =
{
("Total Quantity", NAMEOF ('Measures Table' [Total Quantity]), 0),
("Total Sales", NAMEOF ('Measures Table' [Total Sales]), 1),
("% of Month", NAMEOF ('Measures Table' [% of Month]), 2)
}This creates a table with three columns and three rows in your semantic model. Each row represents the measure or dimension you selected. The first column is the field name, the second column is the field expression using the NAMEOF() function, and the third column is the field order.
It's important to note that field parameters cannot be created via 'New Table' and typing in the code. Field parameters need to be created with Model -> New Parameter -> Fields. Further, the function NAMEOF() is only used in field parameters.
Switch Parameters
Way back in the stone age before Field Parameters existed, we could only use Switch Parameters. These are similar to Measure Parameters in that you can only use them with measures. The main difference (and benefit) is that Switch Parameters give you both x-axis and y-axis values to use in your visuals.
How to Create Switch Parameters
These are built by using a pre-existing table holding a list of your measure names as string values. This can be done either in Power Query, DAX, or brought in from a separate source. It can be a single column with just the list of measure names, or you can include an index/sort order if you'd like. You could even re-use the Measure Parameter table to create a combo Measure & Switch Parameter!
Once you have this table, you need to create a SWITCH measure that "switches" between the selected value of the table. Something like the following:
Switch Measure =
VAR _CurrentMeasure =
SELECTEDVALUE( 'Switch Parameter'[Measure Name] )
RETURN
SWITCH (
_CurrentMeasure,
"Total Sales", [Total Sales],
"Total Quantity", [Total Quantity],
"% of Month", [% of Month],
BLANK()
)To use this parameter, place the column 'Measure Name' in your x-axis field and the 'Switch Measure' in your value/y-axis fields. NOTE: This is a measure! Not a calculated column!
One of the drawbacks of using Switch Parameters is that the format type is not inherited from the original measure. To remedy this situation, you can assign the Switch Measure format to dynamic, and write the following DAX code:
VAR _SelectedMeasure = SELECTEDVALUE( 'Switch Parameter'[Measure Name] )
RETURN
SWITCH(
_SelectedMeasure,
"Total Sales", "$#,###",
"Total Quantity", "#,###",
"% of Month", "0.0%",
"#,###"
)
I hope this helps you understand how you can use Power BI parameters in different ways. Cheers!
This article went over some DAX. If you are not comfortable with DAX or want to learn how to write it like a pro, check out my article here: https://www.antaresanalytics.net/post/2018/10/04/basic-power-bi-dax-concepts-you-should-know
Comentarios