Dax Switch Calculation not working as expected

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])

    )

 

 

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

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] ) 
)

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.

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.