BUG: What If parameter not working

[ToddChitt]

I started testing the What If parameter. It doesn't work. I created a simple table of two columns (Year and Value) with three rows. (See screen shot.) I added a What If Parameter and an appropriate slicer.

Next is a calculated column of adding the parameter to the Value. As shown in the screen shot, the value of the Parameter is 13, But a value of 10 is added to to column [Value] on each row.

[OwenAuger · Accepted solution]

Hi @ToddChitt

What If Parameters are practically only usable within measures, not calculated columns. 

Calculated columns are computed at data refresh, in an empty (i.e. unfiltered) filter context (but within the row context of the table).

For this reason, the expression [Adding Value] within a calculated column will not respond to any filters in the report, and will be evaluated in an empty filter context. This means it takes the default value specified when the parameter was created - it looks like this is 10 in your case.

To get the result you were looking for, you could instead define a measure, using SUMX to iterate over the rows of 'Table1':

Measure producing desired result =
SUMX (
    'Table1',
    'Table1'[Value] + [Adding Value]
)

Regards,

Owen

[OwenAuger · Accepted solution]

Hi @ToddChitt

What If Parameters are practically only usable within measures, not calculated columns. 

Calculated columns are computed at data refresh, in an empty (i.e. unfiltered) filter context (but within the row context of the table).

For this reason, the expression [Adding Value] within a calculated column will not respond to any filters in the report, and will be evaluated in an empty filter context. This means it takes the default value specified when the parameter was created - it looks like this is 10 in your case.

To get the result you were looking for, you could instead define a measure, using SUMX to iterate over the rows of 'Table1':

Measure producing desired result =
SUMX (
    'Table1',
    'Table1'[Value] + [Adding Value]
)

Regards,

Owen

[Anonymous]

Thank you @OwenAuger, I hadn't known about the Calculated Column's behind-the-scenes logic. Was struggling with a similar What-if parameter issue that your post solved 🙂

[ToddChitt · Accepted solution]

@OwenAuger: Thanks, it did the trick.

In reality, I was looking to also NOT add in the Parameter's value if it was the current year, but that was easy enough to add in. My final measure for the sample dataset is this:

Measure producing desired result =
SUMX (
    'Table1',
    'Table1'[Value] + IF('Table1'[Year] <> YEAR(TODAY()), [Adding Value],0)
)

Many Thanks!

[ToddChitt]

@OwenAuger:

Next thing I'm trying to do with my What-If parameter (different project so maybe this is best as a new thread?):

I have a table that is a DAX UNION of several other hidden tables. I'm trying to create another table that will be brought into this UNION, and this table needs to have a column that is driven by some What-If paramters.

Now, I can create a table visualization that includes as one column a MEASURE that is the result of my What-If math logic. But I need that measure as a COLUMN so it can be incorporated into the UNION.

Is it possible to do with with DAX? Maybe using some work around?

Thanks in advance.