Forum Discussion

cruncher's avatar
cruncher
Icon for Helper II rankHelper II
1 year ago
Solved

Dax Switch Calculation not working as expected

My Dax calculation is behaving weird. Something related to evaluation context but couldn't able to understand.   This is sample data. In my data there are 3 rows and two columns (Region and Country...
Need Help
  • v-lgarikapat's avatar
    v-lgarikapat
    1 year ago

    Hi cruncher 

    Thank you for the follow-up questions.

    Since this has already been implemented using Field Parameters but is not filtering dynamically, I would recommend submitting this scenario as a feature request in the official Power BI Ideas forum.

    The Product Team actively reviews suggestions there, and if others in the community upvote it, it may be considered for future updates.

    https://ideas.fabric.microsoft.com/

    I trust this information proves useful. If it does, kindly “Accept as solution” and give it a 'Kudos' to help others locate it easily.
    Thank you.

v-lgarikapat's avatar
v-lgarikapat
Icon for Community Support rankCommunity Support
1 year ago

Hi cruncher ,

Thanks for reaching out to the Microsoft fabric community forum.
danextian techies Thanks for your Prompt Response

Thanks for sharing the issue — you're right, this behavior is due to how DAX evaluates context in visuals, especially when SWITCH and DISTINCTCOUNT are used.

The problem arises because DISTINCTCOUNT(Country) is being evaluated in a broader filter context than expected, and not row-by-row in your matrix visual. So even though Region = SOUTH has only one country (C), your measure still falls through to the last condition and returns Country.

And i have modified the DAX aSs below and its working as expected 
RegionOrCountryDisplay =
SWITCH (
TRUE (),
CALCULATE (
DISTINCTCOUNT ( SalesData[Country] ),
ALLEXCEPT ( SalesData, SalesData[Region] )
) = 1, SELECTEDVALUE ( SalesData[Region] ),
SELECTEDVALUE ( SalesData[Country] )
)

I’ve reviewed the scenario and have uploaded the PBIX file here for your reference.

Please feel free to take a look and let me know if anything else is required from my end. I’d be happy to assist further to help resolve the issue

If this post helped resolve your issue, please consider giving it Kudos and marking it as the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana.

  • cruncher's avatar
    cruncher
    Icon for Helper II rankHelper II
    1 year ago

    Thanks for looking into it. It return blank when North region is selected. It should show 2 Countries. Please check

     

     

  • cruncher's avatar
    cruncher
    Icon for Helper II rankHelper II
    1 year ago

    Thanks Lakshmi for investing time on this. But this is not what I want. I need country in two separate rows so that i can show measures again those rows like sales, profit.

     

    This is problem I am trying to solve.

     

    I have a 4 level hierarchy. Region-->Country--->State--->>City.

     

    In one column in table I want to switch between these 4 fields based on Selection.

     

    For example. If One Region is selected then I want to show Country in first column and sales in 2nd column. If two Regions are selected then I don't want to go to next level and show two regions and their respective sales.

     

    This logic should go from bottom to top i.e City---> Region.

     

    I tried with field parameters but fields parameters are not dynamically changing based on selected value in the slicer.

     

    Please let me know if you need more information on this.

    • v-lgarikapat's avatar
      v-lgarikapat
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi cruncher ,

      Thanks for reaching out to the Microsoft fabric community forum.
      Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
      Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
      Please show the expected outcome based on the sample data you provided.
      If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

      Best Regards
      LakshmiNarayana

      • cruncher's avatar
        cruncher
        Icon for Helper II rankHelper II
        1 year ago

        Thanks v-lgarikapat

         

        Please download the pbix file from Here with sample data.

         

         

        This is a hierarhical data of 5 levels. 

         

        1. If 1 Continent is selected then I want to show Region, Category and Sales.

        2. If more than 1 Continents are selected then I don't want to go to next level and show sales by Continents selected and Category .

        3. Same logic should apply for Region, Country, State and City. For example If 1 country is selected then show sales by city and >1 countries are selected then show sales by countries.

         

        User should be able to to filter by category and above logic should work after filtering the data by category.

         

        Thanks a lot for your time.

         

        Regards,

        cruncher