Forum Discussion
Measure with IF statement causes cross join
Thank you,
I does work but I'm not sure why it works instead of the IF statement.
Also, if I have more than one condition such as
Something like:
IF _result =0 THEN 1 ELSE IF _result <50 THEN 2 ELSE 3
I'm separately trying the following:
(_result = 0 )*1
(_result <50 )*2
(_result >50 )*3
But even (_result = 0 )*1 alone doesn't work aka it gives me a cartesian product.
The goal of this KPI is to give a score (1,2 or 3) depending on a condition based on the original measure.
Thank you
Anonymous try this pattern
MEASURE =
VAR _result = CALCULATE(MAX('FactTable'[NB_PARTICIPANTS])
RETURN
SWITCH(
TRUE,
(_result <> 0 ), _result,
..
..
)
Try to just return a blank if nothing is met.
Do it in the right order that it should be.
P.S. Will appreciate your kudos on my messages
Anonymous if you will not succeed please mention me here tomorrow, and I'll try to do a quick zoom with you together to see your model exactly.
Anonymous also, please watch this great video:
https://www.youtube.com/watch?v=_cT9PB72fu8
maybe the clue to your solution is here 🙂
I just tried this:
KPI Score =
VAR vResult=CALCULATE(MAX('FactTable'[NB_PARTICIPANTS]))
RETURN
SWITCH(TRUE(),
vResult=0,0,
vResult<50,1,2)
Still giving me cartesian product.
So to recap:
(_result <> 0 ) * _result will give me one row but (_result <> 0 ) * 1 will give me multiple rows.
I need to give a score value that is different from the result itself.
Thanks for your help!Anonymous I need to go offline for today.
Can you please by tomorrow share a sample PBIX here with the problem and I'll try to solve and share the solution tomorrow.