Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
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
@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.
Any ideas?
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?
Best Regards,
Jay
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |