TOPN Matrix Filtering Issue

DAX DAX Calculations
1

Hi All,

I’m using the TOPN DAX function in a measure that shows the correct result when it is placed in a table.

When i place the measure in a matrix and add a column it shows all values (Including blankls) how would i apply the filter context to the column as well?

Not looking for a solution as such but any information so I can read and apply would be appreciated.

Item Count = Count(Data[Name])

Top 2 Products = 
VAR TOP2 =
    TOPN ( 2, ALLSELECTED ( Data[Name] ), [Item Count], DESC )
RETURN
    CALCULATE ( [Item Count], KEEPFILTERS( ( TOP2 ) )
)

image

Sample.pbix (18.6 KB)

Thanks

2

@adsa ,

As always, you come with interesting questions and problems.

This one is tricky to debug – I’ve attached a solution, but if you want to work through it on your own, the hints I would give would be to check the context of your Top2 table, and also to look at the IN operator (SQL BI has an excellent article on this).

I’m assuming this is the outcome you’re looking for:

image
image696×243 12.1 KB

I’m sure there’s a simpler way to code this, but the attached solution file contains the DAX code to achieve this result.

I hope this is helpful.

– Brian

eDNA Forum - Top Two Matrix Hide Other Rows Solution.pbix (19.1 KB)

4 Likes
3

Hello @adsa,

Thank You for posting your query onto the Forum.

Well, I’ll not distract you by posting the code since you want to try the solution on your own first.

But as @BrianJ suggested, I can give you a hint that you can adopt the Ranking method more efficiently than using the TOPN logic. And if you don’t want to write any of the code than you can also achieve the same results using the Filter Pane as well.

I’m also attaching the working of the PBIX file for the reference purposes, in case you want to review the code.

Hoping you find this useful. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Sample - Harsh.pbix (22.7 KB)

3 Likes
4

@Harsh ,

Well, sure - if you want to do this in 30 seconds without writing two dozen lines of complex virtual table DAX…but where’s the fun in that? :stuck_out_tongue:

Also, really like your alternative approach of using RankX rather than TopN. These two posts together make for a very interesting combination/comparison of the difference between the two, but how they often can be used to get to the end result.

Great stuff! Thanks very much for posting these alternative approaches - I think the ones with multiple solutioins are often the best and most informative forum threads.

  • Brian
1 Like
5

@adsa

Measure = 
VAR TopProducts = 
    CALCULATETABLE ( 
        TOPN ( 2, VALUES ( Data[Name] ), [Item Count] ),
        ALLSELECTED ( Data )
    )
VAR Result = 
    CALCULATE ( [Item Count], KEEPFILTERS ( TopProducts ) )
RETURN Result

Measure 2 = 
VAR TopProducts = 
    CALCULATETABLE ( 
        TOPN ( 2, VALUES ( Data[Name] ), [Item Count] ),
        ALLSELECTED ( Data )
    )
RETURN 
    SUMX ( KEEPFILTERS ( TopProducts ), [Item Count] )
4 Likes
6

@brian
Thank you for the hint and i nearly figured it out with your hint.

@Harsh
Thank you for ranking solution. I initially started off using the RANKX function but ran into the same issue. So decided to first try out something simipler with TOPN (You have no idea how many times i’ve scrubbed through Sam’s RANKX & TOPN videos :smile: )

Whilst the filter pane is a goode idea, I wanted to write and understand it using DAX. This makes it easier to also incorporate parameters and make the values dynamic.

@AntrikshSharma
Thank you for the assistance.

All of these formulas have been addded to my DAX formula collection in the Analyst Hub!

Unfortunately I can only select one post as the solution but can confirm all of them work!

Once again thank you all

2 Likes
7

@adsa ,

Great - glad that all was helpful to you.

Your question actually inspired my LinkedIn post for tonight on DAX debugging. Keep an eye out… :wink:

  • Brian
1 Like
8

Thanks @BrianJ I’ll keep an eye out :smiley:

1 Like