Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tvaishnav
Helper IV
Helper IV

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

@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

14 REPLIES 14
Anonymous
Not applicable

 
AlB
Super User
Super User

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?

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

 

Capture111.PNG

 

Hello! 

 

I want to calculate distinct values of column A. Which i did by using the distinct function in a measure I created. 

 

Now I want to calculate the total number of "No" for each unique value only. Can anyone please help? @AlB 

 

For example the total should be 3 for "No" as I want to count for Saturday as only 1 not 2 times. 

 

Measure_ = 
COUNTROWS (
    FILTER (
        SUMMARIZE ( Table1, Table1[Day], Table1[NotApplicable] ),
        Table1[NotApplicable] = "No"
    )
)

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Sorry, I have another question for you. I have been trying to figure out but its not working. The below formula is working however I want to add another condition to it:

 

 I want to add if IF('PRC Change Management Pipeline List'[Change Stage]="Withdrawn","" otherwise the below formula

 

Days Form Generated = IF('PRC Change Management Pipeline List'[Accept Change Request]="No",NETWORKDAYS('PRC Change Management Pipeline List'[Open Date],TODAY(),1),
SWITCH('PRC Change Management Pipeline List'[Send form to BSCM],"Yes",NETWORKDAYS('PRC Change Management Pipeline List'[Open Date],IF(ISBLANK('PRC Change Management Pipeline List'[Form Submitted on]),TODAY(),'PRC Change Management Pipeline List'[Form Submitted on]),1),"No",NETWORKDAYS('PRC Change Management Pipeline List'[Open Date],IF(ISBLANK('PRC Change Management Pipeline List'[Close date]),TODAY(),'PRC Change Management Pipeline List'[Close date]),1),NETWORKDAYS('PRC Change Management Pipeline List'[Open Date],TODAY(),1)))
 
Would be glad if you can please help me @AlB 

Thank you so much! Yes, it worked. I am not sure how to "Accept as a solution"..

Just click on "Accept as solution"

 

SU18_powerbi_badge

 

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

There is no option to accept this as solution 😕 Maybe becuase the previous solution of the original thread was already accepted? I checled the whole page. 

Here is a faster calculation.

 

Measure3 =
COUNTROWS ( SUMMARIZE ( 'Table1', 'Table1'[Employee], 'Table1'[Post Date] ) )
   

 

Anonymous
Not applicable

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.

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.