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 SalesT...
DAX
power bi
TopN
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
    Icon for Super User rankSuper 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:]
)