Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vyacheslavg
Helper II
Helper II

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.
 
1 ACCEPTED SOLUTION
AlB
Super User
Super User

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] )
)

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

 

This calculated column formula works as well

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
AlB
Super User
Super User

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] )
)

Thanks for the solution, and even I was "close", yay. Smiley Happy

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.