Forum Discussion
Measure Dax Count if Over
Hi Comunity
I need your help.
What I am trying to achieve is to count, via a measure, the number of training "Never completed" by Name to generate a Databar in this table visual.
The first 4 columns comes from the "employee" table the 2 last columns come from the "Training" table. These 2 above table are link via the employee ID.
table visual
employee table (employee ID= "Driver Id")
training table (employee ID= "dcdriverid")
many thanks kin adavance for your support
kind regrads
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!
7 Replies
Confirm that there is a relationship between the Employee table (Driver Id) and the Training table (dcdriverid).
Create the Measure:
NeverCompletedCount =
CALCULATE(
COUNTROWS('Training'),
'Training'[training Status] = "Never completed"
)Add this measure to your table visual that displays employee details along with their training information.
Enable Data bars to visually represent the count.
If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedInHi Kedar,
thank you for you quick answer!
yes, there is a relationship between the 2 tables employee & training (1:*).
my initial question was more about the count over the table visual if the the employee "never completed" the training.
kind regads
Not sure what you want to achieve the measure from Kedar_Pande should work properly. if you share some example it will become easier to help
thank you Saud968.
this below solution was perfect.
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])
)kind regards
Glad that it helped, please Accept it as a Solution.
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!
