topic Re: DAX code to filter topn in DAX Commands and Tips

DAX code to filter topn

Ang0zyx — Mon, 27 Jun 2022 18:14:00 GMT

Hi everyone,

Could you please help me to convert the below query to dax.the idea is to filter the overall top3 products and show for all countries.

SQL:
Select country,product,sales from SalesTable
where product in
(SELECT distinct product as top3product FROM (SELECT product,sales FROM SalesTable ORDER BY sales)

WHERE ROWNUM<=3)

I tried with below dax but is not working.when I add product and sales to a visual,it's properly showing 3 top products but on adding country, it shows all rows.

Calculate(sum(sales),keepfilters(topn(3,allselected(SalesTable[product]),sum(sales),desc)))

Re: DAX code to filter topn

Jihwan_Kim — Mon, 27 Jun 2022 17:20:05 GMT

Hi,

I am not sure how your data model looks like or how your desired outcome looks like, but please try the below.

new measure: = CALCULATE ( SUM ( SalesTable[sales] ), KEEPFILTERS ( TOPN ( 3, ALLSELECTED ( SalesTable[product] ), CALCULATE ( SUM ( SalesTable[sales] ) ), DESC ) ) )

Re: DAX code to filter topn

Ang0zyx — Mon, 27 Jun 2022 17:50:25 GMT

Hi Jihwan_Kim , It is not working when i add both the dimension in the Visual. Please find the screenshot below.

Top 3 Product Sales =

CALCULATE (

SUM ( SalesTable[Sales] ),

KEEPFILTERS (

TOPN (

ALLSELECTED ( SalesTable[Product] ),

CALCULATE ( SUM ( SalesTable[Sales] ) ), DESC

)

Note:

1. I cannnot use visual filter TopN for some certain reason.
2. the data is in a single table like the Screenshot.

Re: DAX code to filter topn

Jihwan_Kim — Mon, 27 Jun 2022 19:10:14 GMT

Hi,

Thank you for your feedback.

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Total sales measure: = SUM(Sales[Total Sales])

Top3 product sales measure: = VAR topthreeproducts = TOPN ( 3, ALL ( 'Product'[Product] ), [Total sales measure:], DESC ) RETURN CALCULATE ( [Total sales measure:], KEEPFILTERS ( TOPN ( 3, ALL ( 'Product'[Product] ), [Total sales measure:], DESC ) ) )

top3 product sales measure fix: = IF ( CALCULATE ( [Top3 product sales measure:], REMOVEFILTERS ( Country[Country] ) ) = BLANK (), BLANK (), [Top3 product sales measure:] )

Re: DAX code to filter topn

Ang0zyx — Mon, 27 Jun 2022 19:32:17 GMT

Hi Jihwan_Kim , Thanks a lot . It worked perfectly 😀

Re: DAX code to filter topn

Ang0zyx — Thu, 30 Jun 2022 08:31:18 GMT

Hi Jihwan_Kim ,

Initially it worked but later on it failed with a single table. Please find the data,Measure & the issue below.

Total sales measure: =

SUM(Sales[Sales])

Top3 product sales measure: =

VAR topthreeproducts =

TOPN ( 10, ALL ( 'Sales'[Product] ), [Total sales measure:], DESC )

RETURN CALCULATE ([Total sales measure:],KEEPFILTERS (TOPN ( 10, ALL ( 'Sales'[Product] ), [Total sales measure:], DESC ) ) )
top3 product sales measure fix: =IF (CALCULATE ( [Top3 product sales measure:], REMOVEFILTERS ( Sales[Source] ) )

= BLANK (),BLANK (),[Top3 product sales measure:])

Issue: of the rows are missing when it is impleted through dax.

ProductSourceSales

A66	S1	56
A77	S1	29
A169	S1	29
A223	S1	18
A230	S1	1
A231	S1	1
A232	S1	3
A247	S1	29
A301	S2	2
A301	S1	84
A303	S3	5
A303	S2	4
A303	S1	25
A712	S3	21
A712	S2	4
A712	S1	28
A718	S2	1
A729	S3	18
A729	S1	11
A767	S1	33
A769	S4	1
A783	S3	15
A783	S1	34
A794	S3	27
A794	S1	7

Re: DAX code to filter topn

Ang0zyx — Mon, 04 Jul 2022 18:36:22 GMT

Could anyone please help on this issue.

Re: DAX code to filter topn

Jihwan_Kim — Tue, 05 Jul 2022 04:35:47 GMT

Hi,

I am not quite sure if I fully understand what your question is, but please check the below picture and the attached pbix file.

Sales total: = SUM( Data[Sales] )

Sales by product: = CALCULATE( [Sales total:], ALLEXCEPT(Data,Data[Product]))

Sales by product top10: = IF ( [Sales by product:] = CALCULATE ( [Sales by product:], KEEPFILTERS ( TOPN ( 10, ALL ( Data[Product] ), [Sales by product:], DESC ) ) ), [Sales total:], BLANK () )