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.
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!
@LasseMr , to work with slicer you need measure
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
@amitchandak , thank you for the quick support.
Tried to follow your recommendations:
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.
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
@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?
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!
Click here to read more about the December 2021 Updates!
Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.
Mark your calendars and join us for our next Power BI Dev Camp!