Multiple Condition into Value

[Chanleakna123]

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? 

[jdbuchanan71 · Accepted solution]

Thank you @parry2k , I totally missed that.

@Chanleakna123 

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 ()
    )
)

[parry2k]

@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.

[jdbuchanan71]

@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.

[Chanleakna123]

@parry2k just follow on linkedin and youtube 🙂

[Chanleakna123]

hi @parry2k @jdbuchanan71 , all are perfect formula, and i got it ABSOLUTELY RIGHT , 

So BRILLIANT . 
love you all 

[parry2k · Accepted solution]

@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:

[jdbuchanan71 · Accepted solution]

Thank you @parry2k , I totally missed that.

@Chanleakna123 

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 ()
    )
)

[parry2k]

@jdbuchanan71 totally agree with you, but based on the color I assumed it is a measure 🙂

[parry2k]

@jdbuchanan71 based on @Chanleakna123 is using measures in the IF , there is no need of Calculcate

[jdbuchanan71]

@parry2k 

Based on the 'table'[field] layout of the code I assumed it was columns.

[jdbuchanan71]

@Chanleakna123 

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(
)))))))

[parry2k]

@Chanleakna123 can you paste the dax code instead of the image?