Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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] )
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?
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] )
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"
)
)
|
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
Thank you so much! Yes, it worked. I am not sure how to "Accept as a solution"..
Just click on "Accept as solution"
|
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] ) )
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |