cancel
Showing results for
Did you mean:
tvaishnav Regular Visitor

Calculate distinct count in one column for each value in another column

How to calcuate distinct count of post date for each employee in employee column using dax?

Company | Job Number | Employee | Craft Class Description | Hours | Post Date | Day

These are the columns in the dataset.

1 ACCEPTED SOLUTION

Accepted Solutions Super User

Re: Calculate distinct count in one column for each value in another column

You can create this measure then instead. Place it in a card and it will give you the sum. It builds a dynamic table with all employees, calculates the distinct count of dates and then adds it all up. If this is not what you need please post some sample data and explain with an example what the expected result would be.

Measure2 =
SUMX (
DISTINCT ( Table1[Employee] );
"CountPerEmployee"; CALCULATE ( DISTINCTCOUNT ( Table1[Post Date] ) )
);
[CountPerEmployee]
)

5 REPLIES 5 Super User

Re: Calculate distinct count in one column for each value in another column

Try this:

1. Place a matrix visual in your report with Table1[Employee] in rows

2. Create this measure and place it in values of the matrix visual

Measure = DISTINCTCOUNT(Table1[Post Date])

tvaishnav Regular Visitor

Re: Calculate distinct count in one column for each value in another column

I want to use the output in a formula. I cannot use matrix here. I am looking for a aggregated value that I can use in card later.

Let me clarify my this. I want to calculate count of distinct post date for each employee and then sum them up. I want the final scalar value. Can this be done using dax? Super User

Re: Calculate distinct count in one column for each value in another column

You can create this measure then instead. Place it in a card and it will give you the sum. It builds a dynamic table with all employees, calculates the distinct count of dates and then adds it all up. If this is not what you need please post some sample data and explain with an example what the expected result would be.

Measure2 =
SUMX (
DISTINCT ( Table1[Employee] );
"CountPerEmployee"; CALCULATE ( DISTINCTCOUNT ( Table1[Post Date] ) )
);
[CountPerEmployee]
)

tvaishnav Regular Visitor

Re: Calculate distinct count in one column for each value in another column

Thank you! That worked like a charm. Thank you so much. Can you tell me why are you using ; instead of ,(comma) . I got an error when I copied your formula. I had to replace semicolon with comma.

Highlighted Super User

Re: Calculate distinct count in one column for each value in another column

Sure. My OS has a non-US locale where the comma is reserved for the decimal separator so semicolon is used in the code. In US-like locales, like yours I assume, the decimal separator is a dot so the comma can be used in the code.

Cheers

Announcements Top Kudoed Authors
Users Online
Currently online: 309 members 3,451 guests
Recent signins:
• PowerBIcreative • RossP96 • Vucko • LManning3 • BramvanEerden • zbi • engradnansw • glee • blhart216 • mss_powerbi • GWTF • antonsundh • Marko_OK • payalmehakare 