cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LasseMr
Helper I
Helper I

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

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"
)

 

LasseMr_0-1632225778758.png

 

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!

 

8 REPLIES 8
amitchandak
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@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?

 

LasseMr_0-1632229193899.png

 

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

 

 

 

 

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@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!

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.

Hi @LasseMr ,

 

Has the problem been solved?

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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 ?

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!

 

LasseMr_0-1632249714461.png

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors