cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

DAX expression to display measure value based on dynamic parameter

Hi

 

Can anyone help? I have a FACT table that stores various pre-calculated sales amounts expressed in 3 different currencies. So for example one row will have 3 separate columns which store the same amount but expressed in 3 different currencies SalesUSD, SalesEUR and one called SalesLOC (which is just the amount in the invoice currency). These columns however are hidden in the client tool because I want to use a different method of selecting them explained below.

 

There is a parameter table within the data model that sits by itself and is not related to the Fact table. In that parameter table are stored 3 currency codes USD, EUR and LOC.

 

The idea is that the parameter table will be placed in the report and the user selects only one value.

There will be a new measure created just called Sales which effectively should use a case statement along the following logical lines to determine which amount is displayed:

 

If user selects parameter cur code USD then display the value from the column SalesUSD in the table

If user selects parameter cur code EUR then display the value from the column SalesEUR in the table

Else just use SalesLOC  

 

Is it possible to create an expression to achieve this?  All my attempts so far have failed returning various unable to determine context errors

 

Any help would be appreciated

 

Br Alan

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Hi @DaxUser100,

 

Try this formula

Sales =
IF (
    HASONEVALUE ( 'CurrencyTable'[Currency] ),
    //returns a value only if a currency is selected
    SWITCH (
        SELECTEDVALUE ( 'CurrencyTable'[Currency], "" ),
        //currency being selected
        "USD", [SalesUSD],
        "EUR", [SalesEUR],
        [SalesLoc]
    )
)

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

1 REPLY 1
Super User II
Super User II

Hi @DaxUser100,

 

Try this formula

Sales =
IF (
    HASONEVALUE ( 'CurrencyTable'[Currency] ),
    //returns a value only if a currency is selected
    SWITCH (
        SELECTEDVALUE ( 'CurrencyTable'[Currency], "" ),
        //currency being selected
        "USD", [SalesUSD],
        "EUR", [SalesEUR],
        [SalesLoc]
    )
)

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors