Hello,

The other day I was watching a video from SQLBI and for the first time I had a surprising thought. I thought «Hey, I would not do it like this». In DAX as in other languages, there are some things that are just a matter of style, sometimes is more. Which case is this, is up to you. Today it’s not going to be super-long, I promise.

Detecting selection on field parameter

This one is somewhat unexpected. Right when Field Parameters came out, SQLBI wrote and article called «Using SELECTEDVALUE with Fields Parameters in Power BI«. The article described the error produced when you use SELECTEDVALUE on the first column of a Field Parameter. You can check the article, but the expression proposed is a SELECTCOLUMNS over a SUMMARIZE with the 3 columns of the field parameter, and even a check on the number of rows contained, so not really convenient in my opinion*.  Interestingly on the twit where Marco shared the article, Kane Snyder proposed a much cleaner solution (also in my opinion!). He said:

«An alternative is to add a calculated column: New Column = Parameter[Parameter] then use SELECTEDVALUE on that».

What? So easy?? I was completely sold and I continue to be. Field parameters are small tables. Even that huge field parameter I created on the dynamic headers article was around 240 rows. Tiny for Power BI standards, so an extra column does no harm and makes life easier in general (Sorry, Chris!). You can use SELECTEDVALUE just like in any other field. Isn’t that sweet?

* In the recent article Alberto uses a simpler approach using MAX, but then of course he needs to check that there’s only one row selected, so not supper intuitive either

Detecting selection on a Calculation Group

The way that Alberto suggests to detect the active calculation item I found it was very complex. And it’s not just the code itself. The solution requires an extra calculated table.

The complexity starts from the fact that the Calculation Group he has to act as a dynamic measure, has «no protection» so it overrides all measures no-matter-what.

Ever since I saw the live stream from Kane Snyder on Reid Havens channel, I knew that «unprotected» calc groups are very dangerous. And like me, Johnny Winter also knew that. That’s why Johnny authored a pioneering (and mindblowing to me) C# script to create a dynamic measure calculation group and that group had already the protection built-in, meaning that it would only overwrite a measure called «Dummy». Any other measure would be unaffected. I follow the same design pattern and I later extended the script adding some bells and whistles and 2 extra dummy measures for more complex use cases (you can check it out here).

If you use this pattern, you’ll never face the problem that Alberto faced. And you’ll never face many other problems with the same cause. Putting limits to calculation groups is always a good idea, even if it takes a bit more effort. Indeed, the final solution from Alberto includes some degree of conditional application of the logic depending on the measure. In general I rather specify the measures that should be affected rather than those that should be not, because I feel it’s easier to manage and maintain. (For the time intelligence calculation group script I did create a calculated table to store the names of the affected measures, so that if I create a new measure that needs to have time intelligence I can add it once to the table and I don’t have to modify all value and formatstring expressions. You can check the script here.)

Going back to the topic of detecting the selected calculation item, if you build the dynamic measure calculation group with the c# script, you get something like this :

-------------------------------------------------
-- Calculation Group: 'Dynamic Measure CG'
-------------------------------------------------
CALCULATIONGROUP 'Dynamic Measure CG'[Dynamic Measure CG]
    Precedence = 1

    CALCULATIONITEM "Sales Amount" =
        IF (
            ISSELECTEDMEASURE ( [Dummy], [Dummy 2], [Dummy CF] ),
            [Sales Amount],
            SELECTEDMEASURE ()
        )
        FormatString = IF (
            ISSELECTEDMEASURE ( [Dummy], [Dummy 2], [Dummy CF] ),
            "#,0.00",
            SELECTEDMEASUREFORMATSTRING ()
        )

    CALCULATIONITEM "Total Cost" =
        IF (
            ISSELECTEDMEASURE ( [Dummy], [Dummy 2], [Dummy CF] ),
            [Total Cost],
            SELECTEDMEASURE ()
        )
        FormatString = IF (
            ISSELECTEDMEASURE ( [Dummy], [Dummy 2], [Dummy CF] ),
            "#,0.00",
            SELECTEDMEASUREFORMATSTRING ()
        )

    CALCULATIONITEM "Total Quantity" =
        IF (
            ISSELECTEDMEASURE ( [Dummy], [Dummy 2], [Dummy CF] ),
            [Total Quantity],
            SELECTEDMEASURE ()
        )
        FormatString = IF (
            ISSELECTEDMEASURE ( [Dummy], [Dummy 2], [Dummy CF] ),
            "#,0",
            SELECTEDMEASUREFORMATSTRING ()
        )

With such a calculation group, you have nothing to fear from other measures. If you want a measure that shows the calculation item selected, you can just go and check the column of the calculation group, like this:

Selected Calc Item :=
SELECTEDVALUE ( 'Dynamic Measure CG'[Dynamic Measure CG] )

No need for extra tables!

While we are on the topic of the selected calculation item, you might be interested in this older article of mine, where the calculation item name is used inside the value expression, so that the calculation item can be replicated and just by changing the name it changes the behavior. In the example each calculation item applies a different degree of transparency to a HEX code measure. Not necessarily useful, but very cool!

So now it’s up to you! Which will you use?

Continue the conversation on Twitter and LinkedIn