Forum Discussion

charlotte_13's avatar
charlotte_13
Frequent Visitor
1 year ago
Solved

Switching between column values issue

I have defined a field parameter that swiches between two column values (price incl discount vs price excl discount) to be shown on x-axis based on selection. I would like to add some additional logic to this, basically, if more than one country is selected in my country slicer I want an additional switch to the corresponding euro values instead of the default local currency price values present in my field parameter. Have tried different approaches but can't get it to work properly. 

DAX
  • Poojara_D12's avatar
    Poojara_D12
    1 year ago

    Hi charlotte_13 

    To dynamically switch between local currency and Euro values on your chart's x-axis based on the country slicer selection, you'll need a measure to count the selected countries. This measure uses COUNTROWS and FILTER to determine how many countries are currently chosen in the slicer. Next, modify your existing field parameter. Within the field parameter definition, incorporate an IF statement. This IF statement checks the value of the country count measure. If the count exceeds one, the field parameter should include the Euro price columns (e.g., "Price Incl. Discount (EUR)"). Otherwise, if only one or no countries are selected, the field parameter should default to the local currency price columns (e.g., "Price Incl. Discount"). Finally, use this modified field parameter as the source for your chart's x-axis. Now, the chart will automatically display Euro values when multiple countries are selected and local currency values when a single country or no countries are selected. Remember to adjust table and column names to match your data model. This allows for a dynamic currency display based on user interaction.

    Also, try this DAX:

     

    Selected Country Count = 
CALCULATE(
    COUNTROWS(DISTINCT('YourCountryTable'[CountryName])),
    FILTER(
        'YourCountryTable',
        'YourCountryTable'[CountryName] IN ALLSELECTED('YourCountryTable'[CountryName])
    )
)

Field Parameter = 
IF(
    [Selected Country Count] > 1,
    {
        ("Price Incl. Discount (EUR)", 'YourDataTable'[Price_Euro_Incl_Discount]),
        ("Price Excl. Discount (EUR)", 'YourDataTable'[Price_Euro_Excl_Discount])
    },
    {
        ("Price Incl. Discount", 'YourDataTable'[Price_Local_Incl_Discount]),
        ("Price Excl. Discount", 'YourDataTable'[Price_Local_Excl_Discount])
    }
)

     

     

     

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    charlotte_13 

    Create Measures for Local and Euro Prices:
    LocalPriceInclDiscount = SUM('YourTable'[PriceInclDiscount])
    LocalPriceExclDiscount = SUM('YourTable'[PriceExclDiscount])
    EuroPriceInclDiscount = SUM('YourTable'[EuroPriceInclDiscount])
    EuroPriceExclDiscount = SUM('YourTable'[EuroPriceExclDiscount])


    Create a Measure to Switch Between Local and Euro Prices:
    DynamicPrice =
    SWITCH(
    TRUE(),
    [SelectedCountryCount] > 1 && SELECTEDVALUE('FieldParameter'[Parameter]) = "Price Incl Discount", [EuroPriceInclDiscount],
    [SelectedCountryCount] > 1 && SELECTEDVALUE('FieldParameter'[Parameter]) = "Price Excl Discount", [EuroPriceExclDiscount],
    SELECTEDVALUE('FieldParameter'[Parameter]) = "Price Incl Discount", [LocalPriceInclDiscount],

     

    Use the Dynamic Measure in Your Visual

  • charlotte_13's avatar
    charlotte_13
    Frequent Visitor
    1 year ago

    Thank you, but this will generate the sum of the prices, I'm interested in showing each individual price point on the x-axis, sometimes in euro, sometimes in local currency

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,

    Thanks for the solution bhanu_gautam  offered, and i want to offer some more information for user to refer to.

    hello charlotte_13 , the measure changes dynamically, so it is better that use the measure, you can create two measures.

     

    price incl discount =sum(table[price incl discount])
    price excl discount=sum(table[price excl discount])

     

    then set the two measurea as field paramater,  and then you can use dynamic format to format the two measures, you can refer to the following link about it.

    Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

  • Poojara_D12's avatar
    Poojara_D12
    Super User
    1 year ago

    Hi charlotte_13 

    To dynamically switch between local currency and Euro values on your chart's x-axis based on the country slicer selection, you'll need a measure to count the selected countries. This measure uses COUNTROWS and FILTER to determine how many countries are currently chosen in the slicer. Next, modify your existing field parameter. Within the field parameter definition, incorporate an IF statement. This IF statement checks the value of the country count measure. If the count exceeds one, the field parameter should include the Euro price columns (e.g., "Price Incl. Discount (EUR)"). Otherwise, if only one or no countries are selected, the field parameter should default to the local currency price columns (e.g., "Price Incl. Discount"). Finally, use this modified field parameter as the source for your chart's x-axis. Now, the chart will automatically display Euro values when multiple countries are selected and local currency values when a single country or no countries are selected. Remember to adjust table and column names to match your data model. This allows for a dynamic currency display based on user interaction.

    Also, try this DAX:

     

    Selected Country Count = 
CALCULATE(
    COUNTROWS(DISTINCT('YourCountryTable'[CountryName])),
    FILTER(
        'YourCountryTable',
        'YourCountryTable'[CountryName] IN ALLSELECTED('YourCountryTable'[CountryName])
    )
)

Field Parameter = 
IF(
    [Selected Country Count] > 1,
    {
        ("Price Incl. Discount (EUR)", 'YourDataTable'[Price_Euro_Incl_Discount]),
        ("Price Excl. Discount (EUR)", 'YourDataTable'[Price_Euro_Excl_Discount])
    },
    {
        ("Price Incl. Discount", 'YourDataTable'[Price_Local_Incl_Discount]),
        ("Price Excl. Discount", 'YourDataTable'[Price_Local_Excl_Discount])
    }
)