cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tvaishnav Regular Visitor
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
Highlighted
Super User
Super User

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

@tvaishnav 

 

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 (
    ADDCOLUMNS (
        DISTINCT ( Table1[Employee] );
        "CountPerEmployee"; CALCULATE ( DISTINCTCOUNT ( Table1[Post Date] ) )
    );
    [CountPerEmployee]
)

 

5 REPLIES 5
Super User
Super User

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

Hi @tvaishnav 

 

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
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?

Highlighted
Super User
Super User

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

@tvaishnav 

 

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 (
    ADDCOLUMNS (
        DISTINCT ( Table1[Employee] );
        "CountPerEmployee"; CALCULATE ( DISTINCTCOUNT ( Table1[Post Date] ) )
    );
    [CountPerEmployee]
)

 

tvaishnav Regular Visitor
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.

Super User
Super User

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

@tvaishnav 

 

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