Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a scenario where we want to analyze vehicle usage during business hours vs non-business hours. However, the user wants to be able to change the definition of "business hours" on the fly. To accomplish this, I thought I'd try using "What if" parameters.
I've created 2 "What if" parameters:
Business Start Hour = GENERATESERIES(0, 23, 1)
Business End Hour = GENERATESERIES(0, 23, 1)
I then created a calculated column with the following function:
Hour Type =
IF( WEEKDAY(FactTable[ReadingTime],2) < 6 && HOUR(FactTable[ReadingTime]) >= 'Business Start Hour'[Business Start Hour Value] && HOUR(FactTable[ReadingTime]) < 'Business End Hour'[Business End Hour Value], "Regular Hours", "After Hours")
However, the DAX function errors with "An unexpected exception occurred" and PBI generates a dump file. I've tried just using one of the parameters and hard-coding the other value, but I still get the same error. Am I using the "What if" parameter incorrectly? Maybe it's not meant to be used inside an IF condition?
Solved! Go to Solution.
Hi @cmorrow ,
We can create two measures to work on it.
After Hours = CALCULATE(COUNTROWS(Table1),FILTER(Table1,[Measure]="After Hours"))
Regular Hours = CALCULATE(COUNTROWS(Table1),FILTER(Table1,[Measure]="Regular Hours"))
Regards,
Frank
Hi @cmorrow ,
We can create a measure to work on it instead of a calculated column.
Measure = IF ( WEEKDAY ( MAX ( 'Table1'[ReadingTime] ), 2 ) < 6 && HOUR ( MAX ( 'Table1'[ReadingTime] ) ) >= SELECTEDVALUE ( 'Business Start Hour'[Value] ) && HOUR ( MAX ( 'Table1'[ReadingTime] ) ) <= SELECTEDVALUE ( 'Business End Hour'[Value] ), "Regular Hours", "After Hours" )
Also please find the pbix as attached.
Regards,
Frank
@v-frfei-msft Unfortunately, I don't think a measure will work the way I need to use it (in a chart) like below. And I think what it comes down to is what @Vvelarde said, that calculated columns are not dynamic. That's disappointing, but at least it explains the error. I'll have to find another way to do this. Thank you both for your quick responses.
Hi @cmorrow ,
We can create two measures to work on it.
After Hours = CALCULATE(COUNTROWS(Table1),FILTER(Table1,[Measure]="After Hours"))
Regular Hours = CALCULATE(COUNTROWS(Table1),FILTER(Table1,[Measure]="Regular Hours"))
Regards,
Frank
Hi @cmorrow ,
Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.
Regards,
Frank
Hi, 2 things to consider:
1. GenerateSeries Create a table in this case 0,1,2,3---23. To obtain the selection you can use SelectedValue.
2. A calculated column is not dynamic. So you can't modify with the Selected Value od the series.
Regards
Victor
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |