cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Post Prodigy

## Top N in Table or Matrix Table

Top N measures work great on bar charts.

How do we make them work on Tables or Matric Tables?

1 ACCEPTED SOLUTION
Resident Rockstar

Hi @bdehning ,

You need to have a slicer to select the value of N and create two measures for this purpose

1. The Value of N can be selected using either a What if Parameter option or using a table of values. I would recommend using a What if Parameter option

2. A Measure to Rank the column. The DAX you can use for this purpose is below

Top_ranks = RANKX(ALLSELECTED(TableName[Column Name]), [Column to be ranked], ,DESC,Dense)
Note:
Column Name will be the dimension column
Column to be ranked will be the measure column or an expression based on a column
3. A Measure to define which columns to consider based on the Parameter selection for N
Rows_to_consider = IF([Top_ranks] <= SELECTEDVALUE(Parameter[Parameter]), 1, 0)
4. Add this Rows_to_consider measure as a visual filter to the visual and select 1 as the default value

Regards,
Resident Rockstar

Hi @bdehning ,

You need to have a slicer to select the value of N and create two measures for this purpose

1. The Value of N can be selected using either a What if Parameter option or using a table of values. I would recommend using a What if Parameter option

2. A Measure to Rank the column. The DAX you can use for this purpose is below

Top_ranks = RANKX(ALLSELECTED(TableName[Column Name]), [Column to be ranked], ,DESC,Dense)
Note:
Column Name will be the dimension column
Column to be ranked will be the measure column or an expression based on a column
3. A Measure to define which columns to consider based on the Parameter selection for N
Rows_to_consider = IF([Top_ranks] <= SELECTEDVALUE(Parameter[Parameter]), 1, 0)
4. Add this Rows_to_consider measure as a visual filter to the visual and select 1 as the default value

Regards,

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors