Assistance required with formulating Measures

[Lance_CM]

Hi There All,

I just started with Power BI today, love it!

My attached workbook has five (!) measures, that I would appreciate some guidance with?

The calculation logic for each is as follows, I just don’t know how to compile the required syntax,

• !_Scheduled Maint Delay (hrs) =

Calculation Logic,

IF ([Responsibility]="Electrical" OR [Responsibility]="Mechanical")

AND [Scheduled]="Y"

THEN [Total Down Time (hrs)]

END

• ! _Unscheduled Maint Delay (hrs) =

Calculation Logic,

IF ([Responsibility]="Electrical" OR [Responsibility]="Mechanical")

AND [Scheduled]="N"

THEN [Total Down Time (hrs)]

END

• ! _Scheduled Ops Delay (hrs) =

Calculation Logic,

IF ([Responsibility]="Operational")

AND [Scheduled]="Y"

THEN [Total Down Time (hrs)]

END

• ! _Unscheduled Ops Delay (hrs) =

Calculation Logic,

IF ([Responsibility]="Operational")

AND [Scheduled]="N"

THEN [Total Down Time (hrs)]

END

• ! _Top5 Delays by Duration =

Calculation Logic,

I need a parameter that will enable me to filter, and represent only the “Top5” incurred delays by duration?

Here is the shared LINK to my model,

Any assistance will be most appreciated,

Cheers,

Lance

[v-yuta-msft · Accepted solution]

Hi Lance_CM,

Try DAX formula like this and check if they can meet your requirement:

_Scheduled Maint Delay (hrs) = IF ((MAX('G1-Axz'[Responsibility]) = "Electrical" || MAX('G1-Axz'[Responsibility]) = "Mechanical") && MAX('G1-Axz'[Scheduled]) = "Y", MAX('G1-Axz'[Total Down Time (hrs)]))

_Unscheduled Maint Delay (hrs) = IF ((MAX('G1-Axz'[Responsibility]) = "Electrical" || MAX('G1-Axz'[Responsibility]) = "Mechanical") && MAX('G1-Axz'[Scheduled]) = "N", MAX('G1-Axz'[Total Down Time (hrs)]))

_Scheduled Ops Delay (hrs) = IF (MAX('G1-Axz'[Responsibility]) = "Operational" && MAX('G1-Axz'[Scheduled]) = "Y", MAX('G1-Axz'[Total Down Time (hrs)]))

_Unscheduled Ops Delay (hrs) = IF (MAX('G1-Axz'[Responsibility]) = "Operational" && MAX('G1-Axz'[Scheduled]) = "N", MAX('G1-Axz'[Total Down Time (hrs)]))

In addtion, you can use visual level filter to filter Top5 values in any of your column with using any parameter, please refer to: 

https://docs.microsoft.com/en-us/power-bi/power-bi-report-add-filter#add-a-filter-to-a-specific-visu....

Best Regards,

Jimmy Tao

[Lance_CM]

Hi Jimmy,

Appreciate the support mate!

I had a go at introducing the proposed DAX formula without any luck, I get “blank” results for all four measures.

To get the formula logic sorted, can I ask that we please only focus on the following:

• Only Harvest Week 201710,
• Responsibility - Mechanical and Electrical
• Scheduled - N
• Total Down Time (hrs)

I analysed the raw data outside power bi and determined the following:

• Total unscheduled Maint Delay should be = 25.13 hrs

Any additional corrective actions you can suggest will be welcome?

Cheers,

Lance