Forum Discussion

Lance_CM's avatar
Lance_CM
Frequent Visitor
8 years ago
Solved

Assistance required with formulating Measures

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's avatar
    v-yuta-msft
    8 years ago

    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-visualization-aka-visual-filter.

     

    Best Regards,

    Jimmy Tao

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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-visualization-aka-visual-filter.

     

    Best Regards,

    Jimmy Tao

    • Lance_CM's avatar
      Lance_CM
      Frequent Visitor
      8 years ago

      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