Forum Discussion

konradjonsson's avatar
konradjonsson
Helper II
4 years ago
Solved

Dynamic parameters in a variable formula

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 WK...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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's avatar
konradjonsson
Helper II
4 years ago

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's avatar
v-easonf-msft
Community Support
4 years ago

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