If in measure

[Anonymous]

I have a table where the columns are subgroup and limits. I made a measure that ranks output quantity by part number, this is from another table. I would like to compare that measure with the limits of the subgroup. If the rank is greater than the limit, it might be "Stock" otherwise "Discreet"

Thanks in advanced.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Assume your tables are as below

They have relationships as below

Then create measures in Table1

Measure_rank =
RANKX (
    FILTER ( ALLSELECTED ( Table1 ), Table1[subgroup] = MAX ( Table1[subgroup] ) ),
    CALCULATE ( SUM ( Table1[quantity] ) ),
    ,
    DESC,
    DENSE
)

Measure_looklimits = LOOKUPVALUE(Table2[limits],Table2[subgroup],MAX(Table1[subgroup]))

Measure_S/T = IF([Measure_rank]>[Measure_looklimits],"Stock", "Discreet")

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.

My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...