TopN DAX with parameters

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)

Hi CorvanDalfzen,

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

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

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