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 ...
  • 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

v-yuta-msft's avatar
v-yuta-msft
Community 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