‘Quick Measures’ is a feature included in Power BI Desktop April update. It’s still in preview, so you need to enable this feature in order to use it. Inside Power BI Desktop, click ‘File’->‘Options and Settings’->‘Settings’->‘Preview Features’ and check the box beside ‘Quick Measures’, as illustrated in the image below.
Microsoft created an online gallery with several templates for DAX formulas. The ‘Quick Measures’ feature in Power BI Desktop reads this gallery, let the user choose a template and fill the parameters for the template from his own model using a wizard. The images below illustrate how it works and you can read more details here
DAX is, for sure, one of Power BI most scaring features and the new ‘Quick Measures’ feature can have a good impact in changing this. Even being similar to excel formulas in some ways, DAX syntax is still too complex and probably a lot of Power BI users avoid creating custom DAX measures and columns. I would like to hear more from you, how do you use DAX? Do you master its syntax?
It seems to me there are at least three main reasons that make ‘Quick Measures’ very important:
- ‘Quick Measures’ can turn DAX more popular for Power BI users
- ‘Quick Measures’ can illustrate much more Power BI possibilities for the users. Users avoiding the DAX syntax will have a limited use of the Power BI, but the ‘Quick Measures’ will have an important role in changing this scenario.
- Another effect of the reason above, the users will be able to create much better reports and dashboards even without DAX knowledge.
The existing measures in the online gallery were first created by Microsoft, however, Microsoft created a submission process so anyone can create and submit new measures. Since this feature is still in preview, for now, this process is very simple: you only need to post a message into quick measures online forum following a template and the most voted measures will be published to power bi. You can see the submitted measures in the gallery here
I just submitted one measure that I use in my dashboards to create tooltips concatenating the top N categories. This image below was built using the ‘WordWideImportersDW‘ sample database and used in the submission, you may notice the tooltip exposing the top 5 employees with the highest amount of sales for each year.
The DAX expression for this measure is the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Top Employee by Sales Amount= var qty= DISTINCTCOUNT('Dimension Employee'[Employee]) Return if (qty >5; CONCATENATEX( TOPN(5; TOPN(5; ADDCOLUMNS ( VALUES ( 'Dimension Employee'[Employee] ); "Total"; CALCULATE ( SUM ( 'Fact Order'[Sales Amount] ) ) ); [Total];DESC) ); 'Dimension Employee'[Employee] & "(" & Format([Total]/1000000;"Currency") & "M)";", ";[Total];DESC) & " and more"; CONCATENATEX( ADDCOLUMNS ( VALUES ( 'Dimension Employee'[Employee] ), "Total", CALCULATE ( SUM ( 'Fact Order'[Sales Amount] ) ) ); 'Dimension Employee'[Employee] & "(" & Format([Total]/1000000;"Currency") & "M)";", ") ) |
In summary: The expression checks the total number of employees, if there are more than five, it limits the result to five, otherwise, no limit is applied. In both cases, all the employee names are concatenated with the sales amount of that employee, correctly formatted.
The next step for a submission is parameterization, we need to change all specific model information in the expression for parameters. The new expression and its parameters will be like this:
Name: Concatenate Field
Tooltip: The field that will be concatenated
Type: Categorical fieldName: Data Field
Tooltip: The field that will be used to define the top X items to concatenate
Type: Numeric Field/MeasureName: Number of Values
Tooltip: If there are more than this number of values, ‘more…’ will be added to the end of the string
Type: Integers
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Top {Concatenate Field} by {Data Field}= var qty= DISTINCTCOUNT({Concatenate Field}) Return if (qty >{Number of Values}; CONCATENATEX( TOPN({Number of Values}; TOPN({Number of Values}; ADDCOLUMNS (VALUES ( {Concatenate Field} ); "Total"; CALCULATE ( SUM ( {Data Field} ) ); [Total];DESC) ); {Concatenate Field} & "(" & Format([Total]/1000000;"Currency") & "M)";", ";[Total];DESC) & " and more"; CONCATENATEX( ADDCOLUMNS (VALUES ( {Concatenate Field} ); "Total"; CALCULATE ( SUM ( {Data Field} ) ); {Concatenate Field} & "(" & Format([Total]/1000000;"Currency") & "M)";", ") ) |
We can’t try this syntax in power bi, this is only a syntax to submit the measure, we only post this to the forum.
If you like this measure, you can vote for it just clicking the like button here. This page also has a working sample of this measure and a downloadable PBIX file.
You can read more about measures submission here, so you will be able to submit your own measures.
There is room for improvement in this measure. Two possible features, in special, call my attention:
- The aggregation could be changed from ‘Sum’ to another aggregation, for example, ‘Count’ would be specially useful. The aggregation could be parameterized.
- The Format is summarizing the values in millions. It can change a lot, some situations may require a summarization in thousands, other in millions, this could be parameterized.
I also noticed some very interesting new possibilities with ‘Quick Measures’ features, but they don’t exist yet. Power BI has a community site to receive new ideas like these ones:
- It would be great if we can create a corporate quick measures gallery instead of only using the public one. This idea already has 26 votes, you can check it and vote here
- It would also be great if we could submit a new quick measure using a wizard inside power bi desktop and even try it as a private quick measure before submitting. I just submitted this idea, you can vote for it here
Conclusion
This is a very promising feature with the power to make DAX way more popular, allowing users to build better dashboards and reports
Load comments