Dax Switch Calculation not working as expected

[cruncher]

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). There are 2 countries in Region North (A and B) and 1 country in Region South (C). I am viewing as Role=SOUTH which filters data where region is South. Distinct count of country is one in region south. But the switch calculation evaluates to last expression. It should return Region column in Region/Country Matrix view but returns Country which is wrong.

REGION	COUNTRY
NORTH	A
NORTH	B
SOUTH	C

Please help

[v-lgarikapat]

Hi @cruncher ,
Thanks for your follow-up and for pointing that out.

You're absolutely right — the earlier version of the logic using SELECTEDVALUE would return blank when multiple countries exist, which is why the visual appeared empty for the North region.

To address this, I’ve updated the measure to dynamically handle both cases:
DAX:

RegionOrCountryDisplay_v5 =

VAR DistinctCountryCount =

    CALCULATE (

        DISTINCTCOUNT ( 'SalesData'[Country] ),

        ALLEXCEPT ( 'SalesData', SalesData[Region] )

    )

RETURN

    IF (

        DistinctCountryCount = 1,

        MAX ( SalesData[Region] ),

        CONCATENATEX ( VALUES ( 'SalesData'[Country] ), 'SalesData'[Country], ", " )

    )
    For South selection                                                                            For North selection with multiple countries 

I've uploaded the updated PBIX file here for your reference.

Please feel free to review it and let me know if there's anything else needed from my side. 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.

[v-lgarikapat]

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]

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]

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]

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

[v-lgarikapat]

Hi @cruncher Thanks for the respone.

Step 1: Create a measure to count the selections at each hierarchical level
This measure determines how many selections have been made at each slicer level.
Example:
ContinentSelected = COUNTROWS(VALUES(TableName[Continent]))
RegionSelected = COUNTROWS(VALUES(TableName[Region]))
CountrySelected = COUNTROWS(VALUES(TableName[Country]))
StateSelected = COUNTROWS(VALUES(TableName[State]))
CitySelected = COUNTROWS(VALUES(TableName[City]))

Step 2: Create a Dynamic Measure to control drill-down display
Here's a generalized DAX measure to achieve your logic:
DynamicSales =
VAR ContinentCount =
COUNTROWS ( VALUES ( TableName[Continent] ) )
VAR RegionCount =
COUNTROWS ( VALUES ( TableName[Region] ) )
VAR CountryCount =
COUNTROWS ( VALUES ( TableName[Country] ) )
VAR StateCount =
COUNTROWS ( VALUES ( TableName[State] ) )
VAR CityCount =
COUNTROWS ( VALUES ( TableName[City] ) )
RETURN
SWITCH (
TRUE (),
-- Case 1: Single Continent Selected, multiple regions selected
ContinentCount = 1
&& RegionCount > 1,
CALCULATE (
SUM ( TableName[Sales] ),
ALLEXCEPT ( TableName, TableName[Region], TableName[Category] )
),
-- Case 2: Single Region Selected, multiple countries selected
RegionCount = 1
&& CountryCount > 1,
CALCULATE (
SUM ( TableName[Sales] ),
ALLEXCEPT ( TableName, TableName[Country], TableName[Category] )
),
-- Case 3: Single Country Selected, multiple states selected
CountryCount = 1
&& StateCount > 1,
CALCULATE (
SUM ( TableName[Sales] ),
ALLEXCEPT ( TableName, TableName[State], TableName[Category] )
),
-- Case 4: Single State Selected, multiple cities selected
StateCount = 1
&& CityCount > 1,
CALCULATE (
SUM ( TableName[Sales] ),
ALLEXCEPT ( TableName, TableName[City], TableName[Category] )
),
-- Case 5: Multiple continents selected (default high-level)
ContinentCount > 1,
CALCULATE (
SUM ( TableName[Sales] ),
ALLEXCEPT ( TableName, TableName[Continent], TableName[Category] )
),
-- Default case: Show lowest granular level
SUM ( TableName[Sales] )
)

Step 3: Set up visuals in Power BI:
• Matrix Visual:
Use your hierarchical fields (Continent → Region → Country → State → City) in the row axis.
• Values:
Add your created measure DynamicSales.

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]

Thanks for working on this @v-lgarikapat. It seems like you misunderstood the problem.

I need my visual to update dynamically between various columns such as continent, region, country etc. I need calculated column kind of functionality inside the matrix/table visual. It should always show three columns. First is:  Current Level (Which should show either Continent or Region or Country or State or City) as per selection of user. Second Category and third sales. See below pictures.

[v-lgarikapat · Accepted solution]

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]

Hi @cruncher ,

As we haven't heard back from you, we are closing this thread. If you are still experiencing the same issue, we kindly request you to create a new thread we’ll be happy to assist you further.

Thank you for your patience and support.

If our response was helpful, please mark it as Accepted as Solution and consider giving a Kudos. Feel free to reach out if you need any further assistance.

Best Regards,

Lakshmi Narayana

[cruncher]

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

[danextian]

Hi @cruncher 

Measures must return a single value, known as a scalar. Since columns or tables are not scalar values, you need to wrap fields like region and country in an aggregation function such as MIN, MAX, AVERAGE, or SUM to return a valid result. For example:

SWITCH ( 
    TRUE (), 
    country_count = 1 , MAX ( 'table'[region] ), 
    MAX ( 'table'[country] ) 
)

[cruncher]

@danextianI get that but as I mentioned earlier it won't solve my problem. Is there any other way to achieve this ?

[cruncher]

It isn't working as expected. Measure will only give max values for a Region, Country. Basically, I want to switch within same column to dynamically show Region or Country based on country count. Its kind of drill down within same column according the access. If user has access to Multiple Regions then show region. If one region and multiple countries then country.If one region, one country and multiple states then show state and sales/profit or other measures

[techies]

Hi @cruncher please try this measure

REGION/COUNTRY =

VAR COUNTRY_COUNT = CALCULATE(

    DISTINCTCOUNT(country[Country]),

    REMOVEFILTERS(country[Country])

)

RETURN

    IF(

        COUNTRY_COUNT = 1,

        MAX(country[Region]),

        MAX(country[Country])

    )