Forum Discussion
Dax Switch Calculation not working as expected
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.
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.
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.
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.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