topic Re: Display only certain rows of a matrix visual based on a column's rank with DAX in DAX Commands and Tips

Display only certain rows of a matrix visual based on a column's rank with DAX

sim_kay7 — Tue, 07 Dec 2021 13:44:37 GMT

Hello,

I have a matrix visual showing costs and turnover for different products for 2020 and 2021.

I also created measure to get the rank of a product in terms of costs and turnover.

I'm trying to find a way to display only those rows of products where costs in 2021 are within the top3 (without using the filter pane).

Can anybody give a hint how to do it?

Cheers

Simkay

Re: Display only certain rows of a matrix visual based on a column's rank with DAX

VahidDM — Tue, 07 Dec 2021 21:58:41 GMT

Hi sim_kay7

You can create another table with DAX and use that or create measure for those items and filter them to just show the top 3, then add measure as a value.

Share you PBIX file or Sample of data to be able to work on it.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Display only certain rows of a matrix visual based on a column's rank with DAX

Jihwan_Kim — Wed, 08 Dec 2021 04:55:50 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

_Costs: =

VAR yearselect = 2021

VAR newtable =

FILTER ( ALL ( Data ), Data[Year] = yearselect )

VAR topthreecost =

TOPN ( 3, newtable, Data[Costs], DESC )

VAR withoutyearfilter =

SUMMARIZE ( topthreecost, Data[Product] )

RETURN

CALCULATE (

SUM ( Data[Costs] ),

KEEPFILTERS ( ( TREATAS ( withoutyearfilter, Data[Product] ) ) )

)

_Turnover: =

VAR yearselect = 2021

VAR newtable =

FILTER ( ALL ( Data ), Data[Year] = yearselect )

VAR topthreecost =

TOPN ( 3, newtable, Data[Costs], DESC )

VAR withoutyearfilter =

SUMMARIZE ( topthreecost, Data[Product] )

RETURN

CALCULATE (

SUM ( Data[Turnover] ),

KEEPFILTERS ( ( TREATAS ( withoutyearfilter, Data[Product] ) ) )

)

Re: Display only certain rows of a matrix visual based on a column's rank with DAX

sim_kay7 — Wed, 08 Dec 2021 10:28:22 GMT

Thanks Jihwan_Kim, that's almost what I wanted. However when I apply it to my dataset it doesn't show the top3 consistently. I assume this is because in my dataset there can be multiple rows with the same product per year. I don't seem to be able to attach .pbix here, so here is a picture of my data:

How can I modify your code to make it work on this data as well?

Best Regards

Simkay

Re: Display only certain rows of a matrix visual based on a column's rank with DAX

sim_kay7 — Wed, 08 Dec 2021 11:42:42 GMT

Jihwan_Kim I added an intermediate step to summarize the costs per product per year.

Now it works. Thanks for your help!

Here is the full solution:

Cheers, Simkay