Dynamic Axis and Top N - retain rank with legend

[Anonymous]

Hi,

I have the following DAX expression which works well, to a point:

Nielsen - Spend, Dynamic Top N = VAR SelectedTop = SELECTEDVALUE('Top N'[Top N])

RETURN

SWITCH(TRUE(),SelectedTop=0,[Nielsen - Spend, Dynamic],

RANKX(ALLSELECTED('Spend (Nielsen) Dynamic Axis'[Axis]),[Nielsen - Spend, Dynamic])<=SelectedTop,[Nielsen - Spend, Dynamic])

• [Top N] is a WhatIf Parameter
• Dynamic Axis comes from a table I've created that lists each categorisation and all the options that appear within each. In this instance the axis will change from Advertiser to Product and will give a list of each when selected.
• [Nielsen - Spend, Dynamic] is a measure that returns a metric depending on the selection of the dynamic axis. In this instance it's the same value, the sum of spend

I'm using this to have a dynamic axis and Top N feature appear in one chart. Below is an image to illustrate this:

This shows top 10 Products:

This is the same chart showing top 5 Advertisers:

This works exactly how I want it to. However, I have a field that identifies which Advertiser/Product is the Client and which is a Competitor. When I add this into the legend I get more bars that the TopN filter should show, as shown below:

This should show the Top 5 Advertisers, all of which should be Competitors. But instead it's also bringing back one bar for the Client. The field I'm using in my legend is called CompetitorFlag.

I feel like this needs to go into the DAX expression above but I'm not sure where or how. 

Any suggestions?

Thanks,

MarkJames

[Mi2n · Accepted solution]

Hi Mark,

First off, very well articulated question. Kudos for that!

I think the issue could be that the Rank is calculated separately for both the categories (Client and Competitors). To solve that, you can change your DAX expression into the following,

Nielsen - Spend, Dynamic Top N =
VAR SelectedTop =
    SELECTEDVALUE ( 'Top N'[Top N] )
RETURN
    SWITCH (
        TRUE (),
        SelectedTop = 0, [Nielsen - Spend, Dynamic],
        RANKX (
            CROSSJOIN(
                ALLSELECTED ( 'Spend (Nielsen) Dynamic Axis'[Axis] ),
                ALLSELECTED ( '<Table Name>'[CompetitorFlag])
                ),
            [Nielsen - Spend, Dynamic]
        ) <= SelectedTop,  [Nielsen - Spend, Dynamic]
    )

Let me know if this helps.

Regards,

Mi2n

[Anonymous]

Thanks @Mi2n, that has worked beautifully.