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
    6 years ago

    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's avatar
      v-zhenbw-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 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