Forum Discussion

vijay27316's avatar
vijay27316
Frequent Visitor
3 years ago

IF CONDITION check with measure

HI Team,

 

Requirement : Need to pick next value if there is no 50 % [**bleep**. total %]

PBIX File Link : https://drive.google.com/file/d/1i3EWeGxpM_cBCh4s1DBHZZQvgdSWH8aJ/view?usp=share_link

 

 

I have created Measure with Virtal table to get the Next value above 50%, This Measure is called [Pick Next value].

Then when I try to compare it with the [**bleep**. Total %] < [Pick Next value], it is not giving correct results.

 

Please help me to understand,

1. how this [Pickup next value] evaluates in filter context ? correct it if needed.

(or) 2. Let's make [Correct Check] measure to compare only with values and exlude filter context coming from [Pickup next value]

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

     

    Pick Next value = 
var a = SUMMARIZE('Brand Sales','Brand Sales'[Brand],'Brand Sales'[Sales], "@**bleep**.Total", [**bleep**. Total])
var c = minx(filter(a,[@**bleep**.Total]>sumx(a,[Sales])/2),[**bleep**. Total])
return divide(c,sumx(a,[Sales]))

     

  • vijay27316's avatar
    vijay27316
    Frequent Visitor
    3 years ago

    HI Ibendlin

    Thanks for your work.

    WHen I use your code, there is no output returned either 1 (or) 0 in table visual "Pick Next value 2".

    This is code I used as you said above, to create MEASURE. but there is no output

     

     

    Pick Next value 2 =
    var a =
    SUMMARIZE('Brand Sales',
        'Brand Sales'[Brand],'Brand Sales'[Sales],
         "@**bleep**.Total %", [**bleep**. Total %]
    )

    var b =
    ADDCOLUMNS(a,
        "Over50",
        if([@**bleep**.Total %]>sumx(a,[Sales])/2,1,0)
    )

    var c =
    minx(
        filter(b,[Over50]=1),
        [@**bleep**.Total %]
    )

    return
    divide(c,
        sumx(a,[Sales])
    )
      • vijay27316's avatar
        vijay27316
        Frequent Visitor
        3 years ago

        Your output from Pickup Next value MEASURE is not returning 0's and 1's. 

        I'm expecting to have 0's and 1's as ouput. So, that I will filter 1's to show rows.

         

        1's should be returned till the row of 70% and 0's for last row 100 %