Forum Discussion

eriweste's avatar
eriweste
Icon for Helper I rankHelper I
6 years ago
Solved

foreach validate per hour if

Hi all

 

I need to create a report errors based on the following logic

 

Success = Record(row) within that hour (ex 18) contains QUALITYOFVALUE "0" or "2"

Error = No record(row) within that hour (ex 18) contains QUALITYOFVALUE "0" or "2"

 

Ex below

the 12/5 do not contain 0 or 2 within that hour timespan

 

Any ideas

Br

erik

 

METERINGPOINTRECIDTIMESERIESREFRECIDYearQuarterMonthDayHourQUALITYOFVALUEVALUE
7,35999E+17563716808456371680842020Qtr 2May101801.159
7,35999E+17563716808456371680842020Qtr 2May1018200
7,35999E+17563716808456371680842020Qtr 2May111801.58
7,35999E+17563716808456371680842020Qtr 2May111850
7,35999E+17563716808456371680842020Qtr 2May1118200.833
7,35999E+17563716808456371680842020Qtr 2May121850
7,35999E+17563716808456371680842020Qtr 2May1218202.931
 
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi eriweste ,

     

    If you want to show daily succeed or error, we can create a measure to meet your requirement.

     

    Measure = 
var max_hour = MAX('Table'[Hour])
var max_day = MAX('Table'[Day])
var max_Q = MAX('Table'[Quarter])
var max_Y = MAX('Table'[Year])
var x = CALCULATE("succeed",FILTER(ALLSELECTED('Table'),'Table'[Hour]=max_hour && 'Table'[Day] = max_day && 'Table'[Quarter]=max_Q && 'Table'[Year]=max_Y)) 
return
IF(0 in DISTINCT('Table'[QUALITYOFVALUE]) || 2 in DISTINCT('Table'[QUALITYOFVALUE]),x,"error")

     

    The result like this,

     

     

     

     

    BTW, pbix as attached.       

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi eriweste ,

     

    If you want to show daily succeed or error, we can create a measure to meet your requirement.

     

    Measure = 
var max_hour = MAX('Table'[Hour])
var max_day = MAX('Table'[Day])
var max_Q = MAX('Table'[Quarter])
var max_Y = MAX('Table'[Year])
var x = CALCULATE("succeed",FILTER(ALLSELECTED('Table'),'Table'[Hour]=max_hour && 'Table'[Day] = max_day && 'Table'[Quarter]=max_Q && 'Table'[Year]=max_Y)) 
return
IF(0 in DISTINCT('Table'[QUALITYOFVALUE]) || 2 in DISTINCT('Table'[QUALITYOFVALUE]),x,"error")

     

    The result like this,

     

     

     

     

    BTW, pbix as attached.       

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.