Forum Discussion

vyacheslavg's avatar
vyacheslavg
Helper II
7 years ago
Solved

Create column with distinct count on category level

Dear colleagues, 

 

Could you please advise how to create a column with distinct count on a category level.

 

The data is quite simple.

 

product idpart
1red
2green
2green
3blue
3black

 

The result should be 

 

product idpartdistinct count of parts on a product level
1red1
2green1
2green1
3blue2
3black2

 

I tried to play with 

Count of version total for product =
CALCULATE(DISTINCTCOUNT('Table1'[version]), ALL('Table1'[product]))
but it returns the value only on a row level.
 
It is very simple to create a matrix with this result, but I need these values in the full table with count on row level, because it will be a part of further calculation.
Both DAX or Power Query approaches will do, thanks a lot.
 
Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi vyacheslavg

     

    Try this for your new calculated column (close to what you already had):

     

    NewColumn=
CALCULATE (
    DISTINCTCOUNT ( 'Table1'[version] ),
    ALLEXCEPT ( 'Table1', 'Table1'[product id] )
)

3 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi vyacheslavg

     

    Try this for your new calculated column (close to what you already had):

     

    NewColumn=
CALCULATE (
    DISTINCTCOUNT ( 'Table1'[version] ),
    ALLEXCEPT ( 'Table1', 'Table1'[product id] )
)
    • vyacheslavg's avatar
      vyacheslavg
      Helper II
      7 years ago

      Thanks for the solution, and even I was "close", yay. :smileyhappy:

       

       

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    This calculated column formula works as well

     

    =CALCULATE(DISTINCTCOUNT(Table1[part]),FILTER(Table1,Table1[product id]=EARLIER(Table1[product id])))

     

    Hope this helps.