How to pass a filtered table to RankX

Question

I would like RankX to rank products for Var2 and only consider/see products who have a value for Var1 (so only A and B and not C).

Here is an example datamodel including the resulting pivottable enter image description here

And here are the measures I use:

SUM Value = SUM ( Data[Value] )

Var1 Check = CALCULATE ( COUNTROWS ( Data ), Variable[Variable] = "Var1" )

RankX = RANKX ( ALL ( 'Product' ), [SUM Value] )

RankX Filter = IF ( ISBLANK ( [Var1 Check] ), BLANK (), [RankX] )

The idea is that my filtered RankX function (RankX Filter) shows 2 instead of 3 for Product B as only A and B should be considered.

Using DAX Studio I managed to filter my product table accordingly but I don't know whether this the right approach nor how to pass that filtered table to a RankX function.

FILTER (
    ADDCOLUMNS (
        'Product',
        "Var1Check", CALCULATE ( COUNTROWS ( Data ), Variable[Variable] = "Var1" )
    ),
    [Var1Check] = 1
)

Alexis Olson Alexis Olson · Accepted Answer · 2019-04-03T14:58:13

You should just be able to put the filtered table as the first argument in your RANKX function. Something along these lines:

RANKX ( FILTER ( 'Product', Variable[Variable] = "Var1" ), [SUM Value] )

How to pass a filtered table to RankX

1 Answers