Forum Discussion
Multiple Condition into Value
hi all ,
good day ,
i have never got it right on this multiple conditional formula .
the result coming out is not right and show all =1 . seems like i did sth on the formula ,
can you all help me?
Thank you parry2k , I totally missed that.
I think the logic is reversed. All of them are always >= 1 so you are always getting the result from [s.330ml1].
Try it like this, I changed it to a SWITCH to make it easier to read also. I also removed the table name in front of the measure. You should NEVER include the table name when referencing a measure.
Samurai330ml12 = SUMX ( VALUES ( 'All Master Data Customer'[Customer Code] ), SWITCH ( VAR _Value = [Samurai 330ml] RETURN TRUE (), _Value >= 24, [s.330ml24], _Value >= 12, [s.330ml12], _Value >= 6, [s.330ml6], _Value >= 3, [s.330ml3], _Value >= 2, [s.330ml2], _Value >= 1, [s.330ml1], BLANK () ) )Chanleakna123 based on what jdbuchanan71 provided. I would recommend to store measure value in a variable because of performance and for each change:
Samurai330ml12 = SUMX ( VALUES ( 'All Master Data Customer'[Customer Code] ), VAR __value = [Samurai 330ml] RETURN SWITCH ( TRUE (), __value >= 24, [s.330ml24], __value >= 12, [s.330ml12], __value >= 6, [s.330ml6], __value >= 3, [s.330ml3], __value >= 2, [s.330ml2], __value >= 1, [s.330ml1] ) )✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
recommend to store measure in a var because of performance:
11 Replies
jdbuchanan71 totally agree with you, but based on the color I assumed it is a measure 🙂
Chanleakna123 based on what jdbuchanan71 provided. I would recommend to store measure value in a variable because of performance and for each change:
Samurai330ml12 = SUMX ( VALUES ( 'All Master Data Customer'[Customer Code] ), VAR __value = [Samurai 330ml] RETURN SWITCH ( TRUE (), __value >= 24, [s.330ml24], __value >= 12, [s.330ml12], __value >= 6, [s.330ml6], __value >= 3, [s.330ml3], __value >= 2, [s.330ml2], __value >= 1, [s.330ml1] ) )✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
recommend to store measure in a var because of performance:
Chanleakna123 can you paste the dax code instead of the image?
Try putting a CALCULATE() around your whole IF statement. SUMX is an iterator and CALCULATE will force context transition.
Samurai330ml12 = SUMX(VALUES('All Master Data Custoemr'[Customer Code),CALCULATE(IF( IF( IF( IF( IF( )))))))jdbuchanan71 based on Chanleakna123 is using measures in the IF , there is no need of Calculcate
Based on the 'table'[field] layout of the code I assumed it was columns.
Chanleakna123 glad to see it worked out. jdbuchanan71 is a SUPERSTAR 👍
Take a moment to subscribe to my YouTube channel where you will find amazing videos on Power BI, link below. Cheers!!
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
parry2k just follow on linkedin and youtube 🙂
Chanleakna123 I updated my post you marked as an answer so people would see the correct use of the variable from parry2k which is absolutely the better way to do it.
Thank you parry2k , I totally missed that.
I think the logic is reversed. All of them are always >= 1 so you are always getting the result from [s.330ml1].
Try it like this, I changed it to a SWITCH to make it easier to read also. I also removed the table name in front of the measure. You should NEVER include the table name when referencing a measure.
Samurai330ml12 = SUMX ( VALUES ( 'All Master Data Customer'[Customer Code] ), SWITCH ( VAR _Value = [Samurai 330ml] RETURN TRUE (), _Value >= 24, [s.330ml24], _Value >= 12, [s.330ml12], _Value >= 6, [s.330ml6], _Value >= 3, [s.330ml3], _Value >= 2, [s.330ml2], _Value >= 1, [s.330ml1], BLANK () ) )hi parry2k jdbuchanan71 , all are perfect formula, and i got it ABSOLUTELY RIGHT ,
So BRILLIANT .
love you all
