Forum Discussion

Lewdis's avatar
Lewdis
Frequent Visitor
2 years ago
Solved

Local or USD selection

I am currently transfering from Tableau to Power BI and struggle a bit.

 

I am currently using a parameter with a single value picklist that has the option Local or USD. 

 

Then each measure has the formula to pick any of the two options. 

 

Would be great to get some help how to replicate this to Power BI

 

Case [Currency Local/USD]

when "Local" then [Value Local]

when "USD" then [Value USD]

End

Need Help
  • Lewdis's avatar
    Lewdis
    2 years ago

    I used the following formula linking to the parameter

     

    Sales Dynamic =
    SWITCH(
        SELECTEDVALUE(prmSalesCurrency[prmSalesCurrency Fields]),
        "'Dataset Measures'[Sales]",sum('Dataset'[Sales]),
        "'Dataset Measures'[Sales USD]",sum('Dataset'[Sales_USD])
    )

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    The Power BI equivalent is SWITCH  (nested IFs, basically) but you can also use Field Parameters (using the measures)  or Calculation Groups  (which requires an external tool called Tabular Editor).

     

    Field Parameters would be my choice.

    • Lewdis's avatar
      Lewdis
      Frequent Visitor
      2 years ago

      Thanks for your help. I dont fylly get it yet. I have a dataset with Sales in local or USD. In that dataset i also have YTD sales and Budget. I want the parameter to cover all of those options.

       

      Also notice that when I try to use the parameter only for Sales it doesnt show as number. It shows as text.

       

      Thanks 

       

       

       

      • Lewdis's avatar
        Lewdis
        Frequent Visitor
        2 years ago

        I used the following formula linking to the parameter

         

        Sales Dynamic =
        SWITCH(
            SELECTEDVALUE(prmSalesCurrency[prmSalesCurrency Fields]),
            "'Dataset Measures'[Sales]",sum('Dataset'[Sales]),
            "'Dataset Measures'[Sales USD]",sum('Dataset'[Sales_USD])
        )
    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago
      In that dataset i also have YTD sales and Budget. I want the parameter to cover all of those options.

      You may have wanted to mention this earlier.  In that case I would recommend you invest in a proper data model. Add a Calendar table, as well as a FX table (assuming you have monthly FX rates?) . Then you can use a slicer with the currencies based on the FX table, and for all your measures (Sales, Budget, YTD) you can then compute the right value based on the chosen currnecy and time range.

      • Lewdis's avatar
        Lewdis
        Frequent Visitor
        2 years ago

        So its not possible to use the same way to use the same setup as in my file and make it possible to switch between the Local or USD fields?

         

        Case [Currency Local/USD]

        when "Local" then [Sales]

        when "USD" then [Sales_USD]

        End