Forum Discussion
Measure Dax Count if Over
Try this
Create a Measure:
Go to the “Modeling” tab and select “New Measure”.
Use the following DAX formula to create the measure:
Count Never Completed =
CALCULATE(
COUNTROWS(Training),
Training[status] = "never completed"
)
Create a Calculated Column:
In the “Training” table, create a new calculated column to count the “Never completed” trainings for each employee:
Count Over =
CALCULATE(
COUNTROWS(Training),
Training[status] = "never completed",
ALLEXCEPT(Training, Training[id employee])
)
Add the Measure to Your Table Visual:
Add the Count Over column to your table visual along with the other columns from the “employee” and “Training” tables.
This will give you the desired result where the “Count Over” column shows the number of “Never completed” trainings for each employee.Here’s how your table visual should look:
Table
id name training status Count Over
1 thomas training 2 never completed 1
2 charles training 2 never completed 2
2 charles training 4 never completed 2
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!
Hi,
here is an example.
employee & training are linked by "id" & "id employee"
employee table
| id | name |
| 1 | thomas |
| 2 | charles |
training table
| id employee | training | status |
| 1 | training 1 | completed |
| 1 | training 2 | never completed |
| 2 | training 1 | completed |
| 2 | training 2 | never completed |
| 2 | training 3 | completed |
| 2 | training 4 | never completed |
Result expected column called "Count over"
This column is calculated the number of ID where the status is "Never completed"
id "2" appear twice because he never completed the training 2&4. Therefore, the count over column should have 2
result expected (within a Table visual)
| id | name | training | status | Count Over |
| 1 | thomas | training 2 | never completed | 1 |
| 2 | charles | training 2 | never completed | 2 |
| 2 | charles | training 4 | never completed | 2 |
Hopefully it clarify what i would like to achieve.
Thank you in advance.
Try this
Create a Measure:
Go to the “Modeling” tab and select “New Measure”.
Use the following DAX formula to create the measure:
Count Never Completed =
CALCULATE(
COUNTROWS(Training),
Training[status] = "never completed"
)
Create a Calculated Column:
In the “Training” table, create a new calculated column to count the “Never completed” trainings for each employee:
Count Over =
CALCULATE(
COUNTROWS(Training),
Training[status] = "never completed",
ALLEXCEPT(Training, Training[id employee])
)
Add the Measure to Your Table Visual:
Add the Count Over column to your table visual along with the other columns from the “employee” and “Training” tables.
This will give you the desired result where the “Count Over” column shows the number of “Never completed” trainings for each employee.
Here’s how your table visual should look:
Table
id name training status Count Over
1 thomas training 2 never completed 1
2 charles training 2 never completed 2
2 charles training 4 never completed 2
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!