Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
How can I add a measure to the below which states IF Sum of [Rest Time 2] is less than 24 hours in last 6 consecutive days then YES
Solved! Go to Solution.
Hi, @HenryJS
You may modify the 'Result' measure as below. The pbix file is attached in the end.
Result =
var _date = SELECTEDVALUE('Table'[Date])
var _workname = SELECTEDVALUE('Table'[Worker Name])
var totalhour =
CALCULATE(
SUM('Table'[Hour]),
FILTER(
ALL('Table'),
[Worker Name]=_workname&&
[Date]>=_date-5&&
[Date]<=_date
)
)
var totalminute =
CALCULATE(
SUM('Table'[Minute]),
FILTER(
ALL('Table'),
[Worker Name]=_workname&&
[Date]>=_date-5&&
[Date]<=_date
)
)
return
IF(
_date=
CALCULATE(
MIN('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[Worker Name]=_workname
)
),
"No",
IF(
totalhour+INT(DIVIDE(totalminute,60))<24,
"Yes",
"No"
)
)
And you can create the following measure to get the total rest time.
Total Rest Time =
var _date = SELECTEDVALUE('Table'[Date])
var _workname = SELECTEDVALUE('Table'[Worker Name])
var totalhour =
CALCULATE(
SUM('Table'[Hour]),
FILTER(
ALL('Table'),
[Worker Name]=_workname&&
[Date]>=_date-5&&
[Date]<=_date
)
)
var totalminute =
CALCULATE(
SUM('Table'[Minute]),
FILTER(
ALL('Table'),
[Worker Name]=_workname&&
[Date]>=_date-5&&
[Date]<=_date
)
)
var h = totalhour+INT(DIVIDE(totalminute,60))
var m = MOD(totalminute,60)
return
IF(
ISBLANK(h),
IF(
ISBLANK(m),
"00"&":"&"00"&":"&"00",
"00"&":"&m&":"&"00"
),
IF(
ISBLANK(m),
h&":"&"00"&":"&"00",
h&":"&m&":"&"00"
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @HenryJS
Based on your description, I created data to reproduce your scenario. The pbix file is attacehd in the end.
Table:
You may create two calculated column and a meausre as below.
Calculated column:
Hour = HOUR([Rest Time 2])
Minute = MINUTE([Rest Time 2])
Measure:
Result =
var _date = SELECTEDVALUE('Table'[Date])
var _workname = SELECTEDVALUE('Table'[Worker Name])
var totalhour =
CALCULATE(
SUM('Table'[Hour]),
FILTER(
ALL('Table'),
[Worker Name]=_workname&&
[Date]>=_date-5&&
[Date]<=_date
)
)
var totalminute =
CALCULATE(
SUM('Table'[Minute]),
FILTER(
ALL('Table'),
[Worker Name]=_workname&&
[Date]>=_date-5&&
[Date]<=_date
)
)
return
IF(
totalhour+INT(DIVIDE(totalminute,60))<24,
"Yes",
"No"
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-alq-msft thank you.
Below it states "Yes" when there is no row for dates before 07/09/20. It should be "No" because there are no rows before that date.
Is it possible to do that?
Also, how can I add another measure similar to what you've done which returns the Total Rest Time for the last 6 days?
Appreciate your help
Hi, @HenryJS
You may modify the 'Result' measure as below. The pbix file is attached in the end.
Result =
var _date = SELECTEDVALUE('Table'[Date])
var _workname = SELECTEDVALUE('Table'[Worker Name])
var totalhour =
CALCULATE(
SUM('Table'[Hour]),
FILTER(
ALL('Table'),
[Worker Name]=_workname&&
[Date]>=_date-5&&
[Date]<=_date
)
)
var totalminute =
CALCULATE(
SUM('Table'[Minute]),
FILTER(
ALL('Table'),
[Worker Name]=_workname&&
[Date]>=_date-5&&
[Date]<=_date
)
)
return
IF(
_date=
CALCULATE(
MIN('Table'[Date]),
FILTER(
ALL('Table'),
'Table'[Worker Name]=_workname
)
),
"No",
IF(
totalhour+INT(DIVIDE(totalminute,60))<24,
"Yes",
"No"
)
)
And you can create the following measure to get the total rest time.
Total Rest Time =
var _date = SELECTEDVALUE('Table'[Date])
var _workname = SELECTEDVALUE('Table'[Worker Name])
var totalhour =
CALCULATE(
SUM('Table'[Hour]),
FILTER(
ALL('Table'),
[Worker Name]=_workname&&
[Date]>=_date-5&&
[Date]<=_date
)
)
var totalminute =
CALCULATE(
SUM('Table'[Minute]),
FILTER(
ALL('Table'),
[Worker Name]=_workname&&
[Date]>=_date-5&&
[Date]<=_date
)
)
var h = totalhour+INT(DIVIDE(totalminute,60))
var m = MOD(totalminute,60)
return
IF(
ISBLANK(h),
IF(
ISBLANK(m),
"00"&":"&"00"&":"&"00",
"00"&":"&m&":"&"00"
),
IF(
ISBLANK(m),
h&":"&"00"&":"&"00",
h&":"&m&":"&"00"
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-alq-msft Thanks - that's perfect and addresses all issues.
It looks as if (m) returns a single digit instead of double digit for minutes in some instances?
Hi, @HenryJS , you may want to author your measure in this pattern,
Test Sum Time =
IF (
SUMX (
DATESINPERIOD ( 'table'[Date], MAX ( 'table'[Date] ), -6, DAY ),
'table'[Rest Time 2]
) < 24,
"Yes"
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |