Forum Discussion

Ang0zyx's avatar
Ang0zyx
Frequent Visitor
4 years ago

DAX code to filter topn

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)))

DAX
power bi
TopN

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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
        )
    )
)

     

  • Ang0zyx's avatar
    Ang0zyx
    Frequent Visitor
    4 years ago

    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 (
    3,
    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.

     

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      4 years ago

      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:]
)
  • Ang0zyx's avatar
    Ang0zyx
    Frequent Visitor
    4 years ago

    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

    A66S156
    A77S129
    A169S129
    A223S118
    A230S11
    A231S11
    A232S13
    A247S129
    A301S22
    A301S184
    A303S35
    A303S24
    A303S125
    A712S321
    A712S24
    A712S128
    A718S21
    A729S318
    A729S111
    A767S133
    A769S41
    A783S315
    A783S134
    A794S327
    A794S17
    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      4 years ago

      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 ()
)