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

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.

Highlighted
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 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 309 members 3,451 guests
Please welcome our newest community members: