Forum Discussion

vijay273162's avatar
vijay273162
Icon for Helper III rankHelper III
4 years ago
Solved

IF condition HELP

HI

In below table, I need to return 3rd row "GM" Company with "1" in [IF 80%] measure.

I write DAX as.... [IF 80 %] = IF([**bleep**. % of Total] <0.8,1,0)

 
Note : [**bleep**. % of Total] & [Rank] are measures -------- which needs to be used in [IF 80%] ---------- to get "1" in 3rd row.
 

 

power bi
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    Hi vijay273162 ,

     

    Try to create a measure like below:

    IF 80% = 
VAR MIN_ = MINX(ALL('Table'),ABS('Table'[cum %of total]-0.8))
VAR VALUE_ = CALCULATE('Table'[cum %of total],FILTER(ALL('Table'),ABS('Table'[cum %of total]-0.8)=MIN_))
RETURN IF([cum %of total]<=VALUE_,1,0)

     


    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    HI vijay273162 

     

    Do you want to change the condition or just add an exception for GM?

     

    To change the condition:

    [IF 80 %] = IF([**bleep**. % of Total] <0.84,1,0)

     

    to add an exception :

    [IF 80 %] = IF(Max(Company])="GM",1,[**bleep**. % of Total] <0.84,1,0))

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

     

    • vijay273162's avatar
      vijay273162
      Icon for Helper III rankHelper III
      4 years ago

      Thanks for reply.

       

      If I apply filter, with IF condition, it return top 2 rows.

      This will give me [% of total] row total as 61.11%. (only volvo & ford).

      I need to get close to 80% or more as row total, (next row "GM" to get row total as 83.33 %.)

       

      • V-lianl-msft's avatar
        V-lianl-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi vijay273162 ,

         

        Try to create a measure like below:

        IF 80% = 
VAR MIN_ = MINX(ALL('Table'),ABS('Table'[cum %of total]-0.8))
VAR VALUE_ = CALCULATE('Table'[cum %of total],FILTER(ALL('Table'),ABS('Table'[cum %of total]-0.8)=MIN_))
RETURN IF([cum %of total]<=VALUE_,1,0)

         


        Best Regards,
        Liang
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    4 years ago

    IF 80% = VAR _rx=[Rank] RETURN IF(CALCULATE([**bleep**. % of Total],FILTER(ALL(Table[Company]),[Rank]<_rx))<0.8,1,0)