First of all I have to credit Dan Meissner for bringing up the use case in the SML forum. The idea is to add the measure description in the tooltip, somehow. He thought that calc groups might be part of the solution, as they are «aware» of the measures in the filter context and tagged me as he knows my little obsession with calc groups. And indeed Calc groups can be the answer, but not in the way that I first imagined.
For starters, Calculation groups are only aware of the measure they are replacing, so you can’t really refer to other measures in the filter context. It is true that you can read all the filter context with a measure as you can do with the dump filters measure you can create with DAX studio. However the same is not true for measures. The only one aware of the measure is the calculation group, but then it replaces it so, too late.
For some reason I was under the impression that you can «cheat» calculation groups and make them believe that another measure is in the filter context. So if I have a Dummy measure (which is just blank) and I replace it with a dynamic measure calc group, the calculation groups of higher precedence will believe that the measure present is the new one. But no. It does not work this way. While SELECTEDMEASURE() and SELECTEDMEASUREFORMATSTRING() will be the ones of the new measure, SELECTEDMEASURENAME will return just «Dummy» or ISSELETEDMEASURE will only return true if you put [Dummy] as argument. So the plan I had fell into pieces.
But then I had an Idea. I can only read the filter context, ok. Actually that’s enough, because when using a dynamic measure calc group, the name of the active calculation item HAS THE SAME NAME AS THE MEASURE! So the way to check of the measure of the filter context is to check the selected value of the dynamic measure calculation group column!
Now, how can I bring the measure description?! I knew that there is a DMV tab in DAX studio that allow you to extract metadata from your model. I checked and indeed, MDSCHEMA_MEASURES seem to have what I needed. I typed first some measure descriptions in Tabular Editor for the measures (although this can be done in the model view inside PBID), save back the model, run the MDSCHEMA_MEASURES query in dax studio and voilà!
However, can I have this as a data source referring to the some model?! Turns out that you can if you are using premium, as your analysis service instance is accessible through XMLA endpoint. You just need to select SQL Server Analysis Services database as a data source, and fill it with your XMLA endpoint, dataset name and DMV the query we just used in DAX studio. If you are not in premium just export it and load it as a new datasource. Or just write your measure definitions table from scratch in an excel file, your choice.
After that I just removed the «default measure» thing, and selected the only two columns I need, measure name and measure description.
let
#"Model Measures" = AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/Self%20referencing%20measures", "simple contoso store and measures", [Query="select * from $SYSTEM.MDSCHEMA_MEASURES", Implementation="2.0"]),
#"Filtered Rows" = Table.SelectRows(#"Model Measures", each ([MEASURE_CAPTION] <> "__Default measure")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"DESCRIPTION", "MEASURE_NAME"})
in
#"Removed Other Columns"
And indeed you get a nice little table «Model Measures» (you can’t call it just Measures) with all the measures in the model.
So now we only need to build a visual using the dynamic measure calculation group and see if we can bring the measure description on the tooltip. If you don’t have it yet into your toolbelt, go grab the script and add it to your macros/quick actions. We go ahead and create a dynamic measure calc group for the famous 4 measures: Sales Amount, Total Cost, Margin, Margin %.
------------------- -- Measure: [Dummy] ------------------- MEASURE 'Dynamic Measure'[Dummy] = BLANK () Description = "Control the content of this measure by selecting values from Dynamic Measure." --------------------------------------- -- Calculation Group: 'Dynamic Measure' --------------------------------------- CALCULATIONGROUP 'Dynamic Measure'[Dynamic Measure] Precedence = 0 CALCULATIONITEM "Margin" = IF ( ISSELECTEDMEASURE ( [Dummy] ), [Margin], SELECTEDMEASURE () ) FormatString = IF ( ISSELECTEDMEASURE ( [Dummy] ), "#,0.00\ ""€"";-#,0.00\ ""€"";#,0.00\ ""€""", SELECTEDMEASUREFORMATSTRING () ) CALCULATIONITEM "Margin %" = IF ( ISSELECTEDMEASURE ( [Dummy] ), [Margin %], SELECTEDMEASURE () ) FormatString = IF ( ISSELECTEDMEASURE ( [Dummy] ), "#,##0.00 %", SELECTEDMEASUREFORMATSTRING () ) CALCULATIONITEM "Sales Amount" = IF ( ISSELECTEDMEASURE ( [Dummy] ), [Sales Amount], SELECTEDMEASURE () ) FormatString = IF ( ISSELECTEDMEASURE ( [Dummy] ), "#,0.00\ ""€"";-#,0.00\ ""€"";#,0.00\ ""€""", SELECTEDMEASUREFORMATSTRING () ) CALCULATIONITEM "Total Cost" = IF ( ISSELECTEDMEASURE ( [Dummy] ), [Total Cost], SELECTEDMEASURE () ) FormatString = IF ( ISSELECTEDMEASURE ( [Dummy] ), "#,0.00\ ""€"";-#,0.00\ ""€"";#,0.00\ ""€""", SELECTEDMEASUREFORMATSTRING () )
So far so good. Now we need to get to the tooltip part. We’ll use a different measure [Dummy Tooltip] that will return the description of the measure being «impersonated» by the original [Dummy] measure. As mentioned before, SELECTEDMEASURENAME is of no use, so instead we’ll check the active calc item of the dynamic measure calculation group. We’ll make the calc item work only on «Dummy Tooltip» measure, which is blank by default.
---------------------------------------------- -- Calculation Group: 'Measure To Description' ---------------------------------------------- CALCULATIONGROUP 'Measure To Description'[Measure To Description] Precedence = 3 CALCULATIONITEM "get description" = IF ( ISSELECTEDMEASURE ( [Dummy tooltip] ), VAR dynamicMeasureCalcItem = SELECTEDVALUE ( 'Dynamic Measure'[Dynamic Measure] ) RETURN IF ( ISBLANK ( dynamicMeasureCalcItem ), SELECTEDMEASURE (), VAR descr = LOOKUPVALUE ( 'Model Measures'[DESCRIPTION], 'Model Measures'[MEASURE_NAME], dynamicMeasureCalcItem, "" ) RETURN descr ), SELECTEDMEASURE () )
Now we need to create the actual tooltip report page. We can place a KPI card with the «dummy tooltip» measure inside and apply the calc item at the visual level. If we want to display the actual value we can use the «dummy» one as well. If we want to get fancy we can even place it in a matrix and use the a time intelligence calculation group to show current year, previous year and YOY% for instance. And as an icing to the cake a dynamic title that is just
Selected Measure = SELECTEDVALUE ( 'Dynamic Measure'[Dynamic Measure] )
which will return the name of the measure in the scope. Well at least the measure that is being displayed, not the dummy one of course.
All this together looks quite nice if you ask me. Showing Measure Description in tooltip is possible after all.
You can use this approach for pretty much any visual, just remember to use the dummy measure and apply the dynamic measure calculation group, from inside the visual (like in this example) or through the filter pane. They key is that doing this we move the measure to the filter context so it can be «read» from a calc group or other measures and then go get the description from it.
If you want to play with the pbix, download it from here
Thank you for reading!
Regards,