TopN DAX with parameters

CorvanDalfzen · May 29, 2025, 12:34pm

Hi all,

Can somebody help me with the DAX-code, I’m lost.

What I want?
I want the enduser to choose an attribute, choose a metric and choose the TopN (from 1 to 20). As a result I want the TopN per chosen attribute with the chosen metric.

I’ve created 3 parameters:
Attributes = {
(“Customer”, NAMEOF(‘Customer’[Customer]), 0),
(“Product”, NAMEOF(‘Product’[Product]), 1),
(“Category”, NAMEOF(‘Product’[Category]), 2),
(“Subcategory”, NAMEOF(‘Product’[Subcategory]), 3),
(“Reseller”, NAMEOF(‘Reseller’[Reseller]), 4),
(“Business Type”, NAMEOF(‘Reseller’[Business Type]), 5),
(“Country”, NAMEOF(‘Sales Territory’[Country]), 6),
(“Region”, NAMEOF(‘Sales Territory’[Region]), 7)
}

Metrics = {
(“Total Sales”, NAMEOF(‘_Measures’[Total Sales]), 0),
(“Total Cost”, NAMEOF(‘_Measures’[Total Cost]), 1),
(“Total Order Quantity”, NAMEOF(‘_Measures’[Total Order Quantity]), 2),
(“Total Orderlines”, NAMEOF(‘_Measures’[Total Orderlines]), 3)
}

TopN = GENERATESERIES(1, 20, 1)

I created a matrix with Attributes on Rows and Metrics on Values.

Now I want to show in this case the Top3 from Customers with Total Sales.

Can somebody help me with the code?

I also read the article Marco Russo wrote: https://www.sqlbi.com/blog/marco/2022/06/11/using-selectedvalue-with-fields-parameters-in-power-bi/

I tried several different measures but still I haven’t found the right one. So please help.

AdventureWorks Sales.pbix (8.0 MB)

shagufta.tabreen25 · June 2, 2025, 12:48am

Hi CorvanDalfzen,

Try to apply filter in filter pane towards the right end of the visualization pane.

Drag in here the customers column.
Select option as advanced filtering.
Top N- You can select Top 3 customers.

CorvanDalfzen · June 2, 2025, 12:48am

I’ve found my own solution:

SelectedAttribute =
VAR __SelectedAttribute =
SELECTCOLUMNS (
SUMMARIZE ( Attributes, Attributes[Attributes], Attributes[Dimensions Fields], Attributes[Dimensions Order] ),
Attributes[Attributes]
)
RETURN IF ( COUNTROWS ( __SelectedAttribute ) = 1, __SelectedAttribute )

SelectedMetric =
VAR __SelectedMetric =
SELECTCOLUMNS (
SUMMARIZE ( Metrics, Metrics[Metrics], Metrics[Measures Fields], Metrics[Measures Order] ),
Metrics[Metrics]
)
RETURN IF ( COUNTROWS ( __SelectedMetric ) = 1, __SelectedMetric )

Rank =
SWITCH(
TRUE(),
[SelectedAttribute] = “Customer” && [SelectedMetric] = “Total Sales”, RANKX(ALLSELECTED(Customer[Customer]), [Total Sales], DESC),
[SelectedAttribute] = “Customer” && [SelectedMetric] = “Total Cost”, RANKX(ALLSELECTED(Customer[Customer]), [Total Cost], DESC),
[SelectedAttribute] = “Customer” && [SelectedMetric] = “Total Order Quantity”, RANKX(ALLSELECTED(Customer[Customer]), [Total Order Quantity], DESC),
[SelectedAttribute] = “Customer” && [SelectedMetric] = “Total Orderlines”, RANKX(ALLSELECTED(Customer[Customer]), [Total Orderlines], DESC),
[SelectedAttribute] = “Product” && [SelectedMetric] = “Total Sales”, RANKX(ALLSELECTED(‘Product’[Product]), [Total Sales], DESC),
[SelectedAttribute] = “Product” && [SelectedMetric] = “Total Cost”, RANKX(ALLSELECTED(‘Product’[Product]), [Total Cost], DESC),
[SelectedAttribute] = “Product” && [SelectedMetric] = “Total Order Quantity”, RANKX(ALLSELECTED(‘Product’[Product]), [Total Order Quantity], DESC),
[SelectedAttribute] = “Product” && [SelectedMetric] = “Total Orderlines”, RANKX(ALLSELECTED(‘Product’[Product]), [Total Orderlines], DESC),
[SelectedAttribute] = “Category” && [SelectedMetric] = “Total Sales”, RANKX(ALLSELECTED(Categories[Category]), [Total Sales], DESC),
[SelectedAttribute] = “Category” && [SelectedMetric] = “Total Cost”, RANKX(ALLSELECTED(Categories[Category]), [Total Cost], DESC),
[SelectedAttribute] = “Category” && [SelectedMetric] = “Total Order Quantity”, RANKX(ALLSELECTED(Categories[Category]), [Total Order Quantity], DESC),
[SelectedAttribute] = “Category” && [SelectedMetric] = “Total Orderlines”, RANKX(ALLSELECTED(Categories[Category]), [Total Orderlines], DESC),
[SelectedAttribute] = “Subcategory” && [SelectedMetric] = “Total Sales”, RANKX(ALLSELECTED(‘Product’[Subcategory]), [Total Sales], DESC),
[SelectedAttribute] = “Subcategory” && [SelectedMetric] = “Total Cost”, RANKX(ALLSELECTED(‘Product’[Subcategory]), [Total Cost], DESC),
[SelectedAttribute] = “Subcategory” && [SelectedMetric] = “Total Order Quantity”, RANKX(ALLSELECTED(‘Product’[Subcategory]), [Total Order Quantity], DESC),
[SelectedAttribute] = “Subcategory” && [SelectedMetric] = “Total Orderlines”, RANKX(ALLSELECTED(‘Product’[Subcategory]), [Total Orderlines], DESC),
[SelectedAttribute] = “Reseller” && [SelectedMetric] = “Total Sales”, RANKX(ALLSELECTED(Reseller[Reseller]), [Total Sales], DESC),
[SelectedAttribute] = “Reseller” && [SelectedMetric] = “Total Cost”, RANKX(ALLSELECTED(Reseller[Reseller]), [Total Cost], DESC),
[SelectedAttribute] = “Reseller” && [SelectedMetric] = “Total Order Quantity”, RANKX(ALLSELECTED(Reseller[Reseller]), [Total Order Quantity], DESC),
[SelectedAttribute] = “Reseller” && [SelectedMetric] = “Total Orderlines”, RANKX(ALLSELECTED(Reseller[Reseller]), [Total Orderlines], DESC),
[SelectedAttribute] = “Business Type” && [SelectedMetric] = “Total Sales”, RANKX(ALLSELECTED(Reseller[Business Type]), [Total Sales], DESC),
[SelectedAttribute] = “Business Type” && [SelectedMetric] = “Total Cost”, RANKX(ALLSELECTED(Reseller[Business Type]), [Total Cost], DESC),
[SelectedAttribute] = “Business Type” && [SelectedMetric] = “Total Order Quantity”, RANKX(ALLSELECTED(Reseller[Business Type]), [Total Order Quantity], DESC),
[SelectedAttribute] = “Business Type” && [SelectedMetric] = “Total Orderlines”, RANKX(ALLSELECTED(Reseller[Business Type]), [Total Orderlines], DESC),
[SelectedAttribute] = “Country” && [SelectedMetric] = “Total Sales”, RANKX(ALLSELECTED(‘Sales Territory’[Country]), [Total Sales], DESC),
[SelectedAttribute] = “Country” && [SelectedMetric] = “Total Cost”, RANKX(ALLSELECTED(‘Sales Territory’[Country]), [Total Cost], DESC),
[SelectedAttribute] = “Country” && [SelectedMetric] = “Total Order Quantity”, RANKX(ALLSELECTED(‘Sales Territory’[Country]), [Total Order Quantity], DESC),
[SelectedAttribute] = “Country” && [SelectedMetric] = “Total Orderlines”, RANKX(ALLSELECTED(‘Sales Territory’[Country]), [Total Orderlines], DESC),
[SelectedAttribute] = “Region” && [SelectedMetric] = “Total Sales”, RANKX(ALLSELECTED(‘Sales Territory’[Region]), [Total Sales], DESC),
[SelectedAttribute] = “Region” && [SelectedMetric] = “Total Cost”, RANKX(ALLSELECTED(‘Sales Territory’[Region]), [Total Cost], DESC),
[SelectedAttribute] = “Region” && [SelectedMetric] = “Total Order Quantity”, RANKX(ALLSELECTED(‘Sales Territory’[Region]), [Total Order Quantity], DESC),
[SelectedAttribute] = “Region” && [SelectedMetric] = “Total Orderlines”, RANKX(ALLSELECTED(‘Sales Territory’[Region]), [Total Orderlines], DESC)
)

Dynamic TopNFilter =

VAR SelectedTop = SELECTEDVALUE(‘TopN’[TopN])

RETURN

IF(ISFILTERED(‘TopN’),

INT([Rank] <= SelectedTop), 1)

Rows Matrix = Attributes
Values = Metrics
Filter pane matrix: Dynamic TopNFilter = 1