Forum Discussion

HasDWk's avatar
HasDWk
New Member
2 years ago
Solved

POWER BI Matrix Visual - Need to get level wise diffrent values

Hi All,

I've a problem in Power BI Matrix Visual.. Can you please help me ? 
Dax I used :
1. Total Sales = sum(Orders[Sales])
2. 

Sales in level wise =
VAR IsRegionLevel = ISINSCOPE(Orders[Region]) && NOT ISINSCOPE(Orders[Category])
VAR IsCategoryLevel = ISINSCOPE(Orders[Category]) && NOT ISINSCOPE(Orders[Sub-Category])
VAR IsSubCategoryLevel = ISINSCOPE(Orders[Sub-Category])

RETURN
    SWITCH(
        TRUE(),
        IsRegionLevel && HASONEVALUE(Orders[Category]), 100, // Region level when expanded
        IsRegionLevel, [Total Sales], // Region level when not expanded
        IsCategoryLevel, [Total Sales], // Category level
        IsSubCategoryLevel, [Total Sales], // SubCategory level
        BLANK()
    )
Here I have three levels of rows like Region , category and sub category
Requirement : 
1. When we are not expanding the Region , it should show Region wise Total sales 
2. when we expand Region , now we can see category , now Region level we should print a common value (will say 100 ) and category it should show it's own Total sales (check screen shot 2)
3. When we expand Sub - Category , now Region level value should be 100 , category level value should be 100 and sub -category levels should show Total Sales like wise. 
(I'm asking this for developing a complex scenario. I'm checking whether Dax xan identify expanded and not expanded stages)
Can we do so? Using my used dax , I can not fulfill 2 and 3 steps 

here are the screen shots 

Screen shot 1 fulfills our requirement(as usual)
But screen shot 2 : Green color areas should be 100 ..(but when we set it to 100 , screen shot 1 values should not be changed to 100.. it should show Total sales)

Need Help
  • MFelix's avatar
    MFelix
    2 years ago

    Hi HasDWk ,

     

    You cannot determine if the Matrix is open or closed DAX is just checking if inside the calculation you want there is the level you are looking for Region, Category or Sub-Category you are not abble to make any references to matrix  visual layout options.

     

    So in this case using this visualization you are not abble to show it.

     

    Workarounds can be trying to see if you can use a custom visual that can allows this changes (Deneb probably is possible) or making the expand and close be based on bookmarks and different visualizations with different metrics.

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Hi HasDWk ,

     

    You cannot determine if the Matrix is open or closed DAX is just checking if inside the calculation you want there is the level you are looking for Region, Category or Sub-Category you are not abble to make any references to matrix  visual layout options.

     

    So in this case using this visualization you are not abble to show it.

     

    Workarounds can be trying to see if you can use a custom visual that can allows this changes (Deneb probably is possible) or making the expand and close be based on bookmarks and different visualizations with different metrics.