cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tvaishnav
Helper II
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
AlB
Super User III
Super User III

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

 

View solution in original post

7 REPLIES 7
suryaj17
New Member

 
AlB
Super User III
Super User III

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

 

 

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?

AlB
Super User III
Super User III

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

 

View solution in original post

Thanks for the solution. This has helped me!

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.

AlB
Super User III
Super User III

@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 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors