Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Chanleakna123
Post Prodigy
Post Prodigy

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? 

 

 

samurai rank.PNG

2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
Super User

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

 

 

 

View solution in original post

parry2k
Super User
Super User

@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 YouTube.gif 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:

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

11 REPLIES 11
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

@parry2k just follow on linkedin and youtube 🙂

Chanleakna123
Post Prodigy
Post Prodigy

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

 

So BRILLIANT . 
love you all 

parry2k
Super User
Super User

@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 YouTube.gif 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:

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

jdbuchanan71
Super User
Super User

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
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k 

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

jdbuchanan71
Super User
Super User

@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
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors