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 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 )

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

  • 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