DAX code help needed - TestBrakeMachinerie

[Anonymous]

Hi, i have 4 tables. 

1 : Date table

2: Shift Table (Day/Night)

2:  A table that list events, "BrakeTestComplete" for example, and who occured at a precise time/date for a  certain vehicule#

3 : Vehicle List Table

All table are linked, but I would like to know the percentage of machinerie who completed their parking brake test in the Month.

I am able to build a table for each day/shift/vehicle and list the number of Event (Sometimes there's multiple BrakeTest for the same Day/Shift/Vehicle. Formula :

BrakeServiceIndicator = CALCULATE(IF([UtilizationTime]>0;
                                                               IF(COUNTA([EventId])>=1;"OK";"")
                                                               ;BLANK())

                                             ;[EventCategory]="Test_ServiceBrake")

This formula works in a table who contain day/shift/vehicle but not if i remove them, it will always shows OK.

For example : on one particular day, I have 10 vehicules operating ( 10 in the day / 10 in the night)

Day shift 5 have done their test and all have done their in night shift. So the result is 15/20 - 75%.

I would like to have the overall result for the month in percentage : 75% of the vehicule have done their test.

thank you very much

[Kristjan76 · Accepted solution]

Hi there,

You do not give very much info, it would be nice to have a image of the model for example. But I can try to help or at least get you started. I would try something like this

BreakServiceIndicator =
VAR tbl = 
SUMMARIZE(
  ShiftTable;
  ShiftTable[Day/Nigth];
  Dates[Date];
  "Nr Of Vehicles";CALCULATE(DISTINCTCOUNT(Vehicle[VehicleId]));
  "Nr ServiceBrake"; CALCULATE( COUNTROWS(EventsTable);EventsTable[EventCategory] = "Test_ServiceBrake")
)
RETURN
DIVIDE(SUMX(tbl; [Nr ServiceBrake]);SUMX(tbl; [Nr Of Vehicles]))
  

If this does not work, you can copy the SUMMARIZE( ... ) and paste that into a new calculated table in order to get an understanding of why it is not working.

Regards,

Kristjan76

[Anonymous]

It worked ! with some adjustment but it worked ! thank you very much ! 🙂 🙂 🙂