Hello,
I have a measure that is a percentage that can go from 0 to 150% and I need to check this value against criteria levels that goes from 0-85%, 85.01%-90%…115%-120% and from 120.01%. My criteria table looks like:
Level 1 85% 1%
Level 2. 90%. 1.2%
…
Level 5. 115%. 1.8%
Level 6. 120%. 2.1% Max
So if my measure is 87% my level should be Level 2. How can I do this with DAX if it’s possible?
Thanks in advance!
Best regards,
Aracelli
Yes you need to by the sounds of this get into dynamic segmentation (or dynamic grouping) formula techniques.
I’ve covered this extensively in many places.
Here’s probably the best few tutorials from to get you going on this.
Key is understanding how to use supporting tables within your model to create these groupings.
https://blog.enterprisedna.co/2018/06/26/secondary-table-logic-inside-of-power-bi-using-dax/
Thanks Sam, i have checked all the resources and i applied the dynamic grouping and it worked good but when i changed the text in the dynamic value by a numerical value, the formula didnt work anymore:
Dynamic VB grouping1 = calculate(SELECTEDVALUE('Criteria_Bonus_1 0'[Criteria],blank()),filter(all('Criteria_Bonus_1 0'),[Results over Targets]>='Criteria_Bonus_1 0'[Min_Value]&&[Results over Targ,ets]<'Criteria_Bonus_1 0'[Max_Value]))
Error:
MdxScript(Model) (9,143) Calculation error in measure Dynamic VB grouping1: DAX comparison operations do not support comparing values of type Number with values of type Text.
Here is my criteria supporting table
_
_
Couple of things that stick out.
First these look like text values when you should be numeric
It’s likely you want this column to be the text value instead of numeric
Also I want to really recommend starting to work on formatting your DAX measures a bit better.
Once you start getting into more complex formula patterns ideas like this and the formula become longer you want it to be easy to scan and understand exactly what’s going on.
See here for a few ideas on this
Even I find it hard to understand formula when written out as per here
Hi Sam, yes, sorry, i am new on this and i was trying to test the formula, it’s why i didnt format it. But formatted looks like this:
Dynamic VB grouping1 =
CALCULATE (
SELECTEDVALUE ( 'Criteria_Bonus_1 0'[Criteria], BLANK () ),
FILTER (
ALL ( 'Criteria_Bonus_1 0' ),
[Results over Targets] >= 'Criteria_Bonus_1 0'[Min_Value]
&& [Results over Targets] < 'Criteria_Bonus_1 0'[Max_Value]
)
)
Now that I changed the data type, it works perfectly. Thanks a lot!!!
Ok super that’s great.