Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Select topn value without duplicate

Hi floks, Here I facing an difficulties, that want to select TOP 20 rows from the table without duplicate of the same values. I have tried these DAX to achive but it returns dupliacte rows. DAX Qu...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Anonymous ,

     

    We create a sample, that Total has two rows same value.

     

     

    Then we can create a calculate table using the following formula.

     

    Table 2 = 
TOPN (
    20,
    SUMMARIZE (
        'factQuotes',
        'factQuotes'[Total],
        "name", CALCULATE ( MAX ( 'factQuotes'[name] ) )
    ),
    'factQuotes'[Total], ASC
)

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    BTW, pbix as attached.

v-zhenbw-msft's avatar
v-zhenbw-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

We create a sample, that Total has two rows same value.

 

 

Then we can create a calculate table using the following formula.

 

Table 2 = 
TOPN (
    20,
    SUMMARIZE (
        'factQuotes',
        'factQuotes'[Total],
        "name", CALCULATE ( MAX ( 'factQuotes'[name] ) )
    ),
    'factQuotes'[Total], ASC
)

 

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi v-zhenbw-msft ,
Thank you for your help.
Actually by your solution, I have change some data's in datawarehouse now its perfectly works as expected.


Thanks,
Sivanesan C