Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DaxUser100
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
danextian
Super User
Super User

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

1 REPLY 1
danextian
Super User
Super User

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.