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.
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
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |