Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

IF or SWITCH statement depending on the selected value of another column

Hi,

 

I need a measure that will evaluate a column named "Company" and return a specific currency in the "Currency" column based on the "Company" selection. So in my "Company" column, I have Company A, Company B, Company C, and Company D. In my "Currency" column, I has either BRL or USD. Companies A and B are Brazilian companies, and I have figures for these companies in both BRL and USD currencies. Companies C and D are US companies, so figures for them are only in USD. 

 

I have a clustered column chart showing revenue for each company while "Company" is placed in a slicer. When one of the Brazilian companies (Company A and Company B) is selected, I want figures to be displayed in BRL currency. If one of the non-Brazilian companies (Company C and Company D) is selected, I want figures to be displayed in USD currency. I intend to add this measure as a filter & it work properly.

 

I have tried the following two measures and neither is functioning, and bother return either TRUE or FALSE rather than the desired currency being selected.

 

Currency Fix =
IF(SELECTEDVALUE(Append2[Company])="Company A", SELECTEDVALUE(Append2[Currency])= "BRL", SELECTEDVALUE(Append2[Currency])="USD") &
IF(SELECTEDVALUE(Append2[Company])="Company B", SELECTEDVALUE(Append2[Currency])= "BRL", SELECTEDVALUE(Append2[Currency])="USD")
*Note: I have tried a variation of this IF statement by doing a nested IF statement. Sames incorrect results were given. 
 
Currency Fix =
SWITCH( TRUE(),
SELECTEDVALUE(Append2[Company]) = "Company A", SELECTEDVALUE(Append2[Currency]) ="BRL",
SELECTEDVALUE(Append2[Company]) = "Company B", SELECTEDVALUE(Append2[Currency]) ="BRL")
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

     

    SELECTEDVALUE(Append2[Currency]) ="BRL" is a logical condition statement, so it will always return a logical result True/False.

     

    In your scenario, if your table structure is like below ("BRL" and "USD" are both in the Currency column), you could create a measure to calculate the value you need to populate the column chart. For example,

    Amount measure = 
IF (
    SELECTEDVALUE ( 'Table (3)'[Company] ) IN { "Company A", "Company B" },
    CALCULATE ( SUM ( 'Table (3)'[Amount] ), 'Table (3)'[Currency] = "BRL" ),
    SUM ( 'Table (3)'[Amount] )
)

     

    You could modify the measure per your need.

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Your question is ambiguous.  Do you need to return the currency string, or do you need to return a monetary value (possibly even with FX applied)?

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous 

     

    SELECTEDVALUE(Append2[Currency]) ="BRL" is a logical condition statement, so it will always return a logical result True/False.

     

    In your scenario, if your table structure is like below ("BRL" and "USD" are both in the Currency column), you could create a measure to calculate the value you need to populate the column chart. For example,

    Amount measure = 
IF (
    SELECTEDVALUE ( 'Table (3)'[Company] ) IN { "Company A", "Company B" },
    CALCULATE ( SUM ( 'Table (3)'[Amount] ), 'Table (3)'[Currency] = "BRL" ),
    SUM ( 'Table (3)'[Amount] )
)

     

    You could modify the measure per your need.

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      This worked perfectly! Thank you so much!!