Forum Discussion

Chanleakna123's avatar
Chanleakna123
Post Prodigy
4 years ago
Solved

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 ...
  • jdbuchanan71's avatar
    4 years ago

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

    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: