Forum Discussion
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 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
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.
9 Replies
Anonymous - Perhaps try adding a small RAND() number to break ties.
Anonymous - Throw in a DISTINCT somewhere?
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/367415Hi 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.
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 CHi 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.
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
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
