"What if" parameters - An unexpected exception occurred

[cmorrow]

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?  

[v-frfei-msft]

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

[cmorrow]

@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.

[v-frfei-msft · Accepted 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

[v-frfei-msft]

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

[Vvelarde]

@cmorrow 

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