Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago
Solved

Count total categories without repeating by ID

Hello everyone

I've been trying to do a total count of unique categories by ID for some time and there is no way. The issue is that I have some IDs and each ID can have N categories. This category can be repeated both in the same ID and appear in other IDs. What I want is to count only 1 time that category per ID. Is it possible to do it in a measure with DAX? Attached image so that it is better understood:

Having the table on the left I want to add 9 which are the unique categories by ID.

Thank you very much in advance

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Syndicate_Admin ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a measure. 

    Unique Categories by ID = 
    COUNTROWS(
        SUMMARIZE(
            'Table',
            'Table'[ID],
            'Table'[Categoria]
        )
    )

    (3) Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Syndicate_Admin ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a measure. 

    Unique Categories by ID = 
    COUNTROWS(
        SUMMARIZE(
            'Table',
            'Table'[ID],
            'Table'[Categoria]
        )
    )

    (3) Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

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