Select topn value without duplicate

[Anonymous]

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 Query: TOPN(20,factQuotes,factQuotes[TotalValue],DESC)
it returns like shown below

Can any one please help me to crack this.


Thanks in advance,
Sivanesan C

[SonalPowerBI]

This solution is not working for me. When I create new table same like above, I not getting fields which I require. Is there any other solution. how to resolve tie when showing TOP 5 as I am getting more than TOP N values

[v-zhenbw-msft · Accepted solution]

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]

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

[Anonymous]

Hi @v-zhenbw-msft ,
Really very thankfull to you.
I got the partial solutions from that, because in my data I have replication on Quote ID aslo.
If you have any idea, please help me to get out it.


Thank you,
Sivanesan C

[v-zhenbw-msft]

Hi @Anonymous ,

Sorry for that we don’t know which column is Quote ID, maybe you can refer the following method.

1. In Power Query Editor, remove the duplicates in the columns that you want to remove the duplicates.

2. Then we need to sort the Total column.

3. Then we add an index column.

4. At last we can filter the data that Index is less than or equal to 20. Or we can create a new table using Filter function.

Table = SUMMARIZE(FILTER(factQuotes,factQuotes[Index]<=20),factQuotes[name],factQuotes[Total])

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

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.

[amitchandak]

@Anonymous , for this you need add a very small random number while creating the rank

https://docs.microsoft.com/en-us/dax/rand-function-dax -RAND() 

Refer the approach in - Same should work in TOP N

https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/

https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Greg_Deckler]

@Anonymous - Perhaps try adding a small RAND() number to break ties.

[Anonymous]

Hi @Greg_Deckler ,
I have try to add RAND(), But again I get the value in top @20 

[Greg_Deckler]

@Anonymous - Throw in a DISTINCT somewhere?