Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Measure with IF statement causes cross join

I have a table visual. I've created a simple measure:  Total_participants=CALCULATE(MAX('FactTable'[NB_PARTICIPANTS])) When adding this measure to the visual, the number of rows is correct aka one ...
Need Help
SpartaBI's avatar
SpartaBI
Community Champion
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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!





  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    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.