Measure Dax Count if Over

[X19203659]

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

[X192036598]

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

[saud968]

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!

[X192036598]

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.

[saud968 · Accepted solution]

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!

[Kedar_Pande]

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 LinkedIn

[X192036598]

Hi 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

[saud968]

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