Dynamic parameters in a variable formula

[konradjonsson]

Hi.

I want to make two parameters of my formula dynamic, i.e. replace the fixed values with values that the person running the report is selecting. The specific values are WKCurrencyType (=2) and WKRreportCurrency (=133).

-these two I would like to be able to select while running the report.

What solutions are there for this?

CURRENT FORMULA

ExchangeRate =

VAR _date = 'Sales'[WKInvoiceDate]

VAR _currency = 'Sales'[WKCurrency]

RETURN

CALCULATE( MAX('Exchange Rates'[ExchangeRates] ), year('Exchange Rates'[WKDate]) = year(_date), 'Exchange Rates'[WKSourceCurrency] = _currency, 'Exchange Rates'[WKCurrencyType]=2,'Exchange Rates'[WKReportCurrency]=133 )

[konradjonsson]

I created a What if-parameter, to replace the fixed currency settings (133). I got an error message saying; "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

I do not how to address this.

[v-easonf-msft · Accepted solution]

Hi， @konradjonsson 
If [WKInvoiceDate],[WKCurrency],[parameter1 value],[parameter2 value] are measure columns, try to encapsulate all conditions with filter function.

Measure:

ExchangeRate =
VAR _date = 'Sales'[WKInvoiceDate]
VAR _currency = 'Sales'[WKCurrency]
RETURN
    CALCULATE (
        MAX ( 'Exchange Rates'[ExchangeRates] ),
        FILTER (
            'Exchange Rates',
             YEAR ( 'Exchange Rates'[WKDate] ) = YEAR ( _date )
                && 'Exchange Rates'[WKSourceCurrency] = _currency
                && 'Exchange Rates'[WKCurrencyType] = [parameter1 value]
                && 'Exchange Rates'[WKReportCurrency] = [parameter2 value]
        )
    )

Best Regards,
Community Support Team _ Eason

[konradjonsson]

Thanks! Will try this.

[Anonymous]

Hi, you can use this “New parameter”,in Modeling

[amitchandak]

@konradjonsson, You can use what if the parameter of some other slicer value 

CURRENT FORMULA

ExchangeRate =

VAR _date = 'Sales'[WKInvoiceDate]

VAR _currency = 'Sales'[WKCurrency]

RETURN

CALCULATE( MAX('Exchange Rates'[ExchangeRates] ), year('Exchange Rates'[WKDate]) = year(_date), 'Exchange Rates'[WKSourceCurrency] = _currency, 'Exchange Rates'[WKCurrencyType]=selectedvalues(whatif1[Value]) ,'Exchange Rates'[WKReportCurrency]=selectedvalues(whatif2[Value]) )

https://docs.microsoft.com/en-us/power-bi/desktop-what-if