Getting Model Parameter to work with Switch in Calculated Column (classic ABC case)

[LasseMr]

Dear community,

Does anyone know why a selectedvalue() from a model parameter approach does not work well in a calculated column using the switch case approach?

ABC From Parameter shows incorrectly C in all rows whereas when I use a fixed measure value it is able to classify A, B, C correctly.

Switch case:

ABC From Parameter =

SWITCH(TRUE(),

[Running Total %]<=[A Value],"A",

[Running Total %]<=[C Value],"B",

[Running Total %]>[C Value],"C"

)

I have uploaded the sample PBIX to this shared link: https://dbizdk-my.sharepoint.com/:u:/g/personal/lasse_dbiz_dk/EYfQ7Lshf1FAvN71loHD7y4B5k5sJRVsWE6-LL...

Curious to understand this better!

Thanks a lot!

[amitchandak]

@LasseMr , to work with slicer you need measure

example

ABC From Parameter m = 
SWITCH(TRUE(),
    min([Running Total %])<=[A Value],"A",
    min([Running Total %])<=[C Value],"B",
    min([Running Total %])>[C Value],"C"
    )

Also running total should be a measure

[LasseMr]

@amitchandak , thank you for the quick support.

Tried to follow your recommendations:

1.  added a Running Total % Measure as a Measure instead of Calc Column.
2.  added a ABC classification with reference to Measure instead of Calc Column, I can't add a min() around them though as they are of type measure and not column now.

As you can see on the result, the Running Total % Measure still shows correct % per item, but the ABC From Parameter Amit still doesn't work correctly.

Any ideas?

PBIX sample file updated here: https://dbizdk-my.sharepoint.com/:u:/g/personal/lasse_dbiz_dk/EYfQ7Lshf1FAvN71loHD7y4B5k5sJRVsWE6-LL...

[amitchandak]

@LasseMr , everything is correct. The column with my name needs to be a measure 😆.

Just create that as a measure, the one you have shown in the screenshot. File attached after signature

[LasseMr]

@amitchandak , you have sent me in the right direction to search for the answer, and it seems to be a dead end, that Power BI does not allow slicer values as part of a calculated column formula. I.e. not posible to make calculated columns "dynamic".

I found the following link: https://community.powerbi.com/t5/Desktop/Slicer-Value-in-Column-Formula/td-p/214892

It annoys me quite a bit because it would be a tremendous great feat for the users to be able to simulate the parameters themselves and provide them with the result of items per category.

Do you know of a workaround?

Thank you for your help in any case!

[LasseMr]

Closest I have found becomes a bit more advanced: https://www.goodly.co.in/measures-in-columns-matrix-powerbi/

Gotta be a smarter way around, anyways, I'll give that a try later.

[v-jayw-msft]

Hi @LasseMr ,

Has the problem been solved?

Best Regards,

Jay

[LasseMr]

Hi @v-jayw-msft ,

No, unfortunately no.

I have learned that Power BI does not support dynamic calculated columns, which I kind of understand the technical reason "why" - i.e. being in control of memory and CPU after a dataset is published and only updated at "refresh".

I am still thinking that there could be a work around, but it gets much more complicated than what I have had time and competences for, i.e. I am thinking following could draw inspiration for something that "could work": https://www.goodly.co.in/measures-in-columns-matrix-powerbi/

Basically, what I would like to be able to do is making a matrix with ABC (and later XYZ) on rows with count of unique items in values and being able to click e.g. the amount of A items and having the table below with the details filter all A items. Right now it works if I fix the measure-levels between A,B,C items in a calculated column, but it does not work if I make the measure-levels between A,B,C parameter controlled with a slicer, i.e. making the input to the calculated column dynamic, so the user can simulate on their own the impact of different levels.

See attached test-PBIX from earlier post.

Do you see other options @v-jayw-msft ?

[LasseMr]

Argh @amitchandak , you are right, it does work as a measure, in the per item table.

But now I have another problem, then I can't use the ABC measure in a table/matrix visualization as a row/columns to visualize the count of items of each category 🙂

I am so close, yet getting to the final step solution seems so difficult!