Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
eriweste
Helper I
Helper I

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
 
1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

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,

 

For 1.jpg

 

For 2.jpg

 

For 3.jpg

 

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.

View solution in original post

1 REPLY 1
v-zhenbw-msft
Community Support
Community Support

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,

 

For 1.jpg

 

For 2.jpg

 

For 3.jpg

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.