cancel
Showing results for
Did you mean:
Helper II

## 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
Super User III

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

7 REPLIES 7
New Member

Super User III

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

Helper II

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 III

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

Helper IV

Thanks for the solution. This has helped me!

Helper II

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.

Super User III

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

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks