Dynamic dimension in a matrix with slicer

[modnacc]

Hi

I have 2 Tables: 

Table1: contains Country, State ,City, area and storename columns.

Table2: contains , Businesswith(Proc1, Proc2, Proc 3, Proc 4) , area and storename:

Table 2 contains only those stores which have investment from any of the Proc1,2,3, or 4. this means Table2 is sort of subset of Table 1.
Table 2 can have 2 rows where storename is same with Proc name also same , city also same .

The requirement is to develop a matrix visualization which allow a slicer from Table 1 as field parameter, containing Country, state ,city and area.
and it shows storenames under that area. 

in front of the store name it says "checked" while in front of area it shows the count of store name under that area, 
similary it should count the storenames as the hiearchy goes up 
area-->city-->state--->country
as an example 

Country1					8
	State A				8
		City-1			5
			area`1		3
				store1	CHecked
				store2	Checked
				store3	Checked
			area2		2
				store1	CHecked
				store2	Checked
		City-2			3
			area`1		1
				store1	CHecked
			area2		2
				store1	CHecked
				store2	Checked

i have been able to build the dynamic dimension with field parameter coming from the table 1 and Proc in table 2. but somehow this count is causing problem 
it dynamincally writes checked, and also s hows count of stores correctly at area level but wrong count is being displayed at city , and state level upto country level 

sample dax is as below: 

"

BS_Disp_Corrected =

VAR storecount = COUNT('T2'[storename])

RETURN

SWITCH(

    TRUE(),

    -- ✅ Show "checked" at the store Level

    ISINSCOPE('T2'[storemame]), "YES",

    -- ✅ At the area level, count distinct storenames they invested with

    ISINSCOPE('T2[area]), storeCount,

    -- ✅ At the city level, SUM stores from **all areas under that city**

    ISINSCOPE('T1'[city]),

    SUMX(

        VALUES('T1'[area]),

        CALCULATE(COUNT('T2'[store]))

    ),

    -- ✅ At the state level, SUM distinct store from **all city under that state**

    ISINSCOPE('T21'[state]),

    SUMX(

        VALUES('T2'[city]),

        CALCULATE(COUNT('T1'[storename]))

    ),

    -- ✅ At the country level, SUM distinct customers from **all states under that country**

    ISINSCOPE('T2'[country]),

    SUMX(

        VALUES('T2_'[State]),

        CALCULATE(COUNT('T1'[Store]))

    ),

    -- ✅ At the Proc level, SUM distinct stores across all hierarchy levels

    ISINSCOPE('T1[Proc]), storeCount,)

    -- Default case

    BLANK()

)

appreciate your help to rectify.  tables are joined by store name, but they can also be joined by areas

Thanks 

"

[Preeti_G · Accepted solution]

Hi @modnacc ,
Your DAX measure is close to what you need, but there are a few issues causing incorrect counts at the city, state, and country levels. Instead of count use Distinctcount which could solve your problem.

Corrected DAX Measure

BS_Disp_Corrected =
VAR storeCount = DISTINCTCOUNT('T2'[storename])
RETURN SWITCH( TRUE(),
-- Show "Checked" at the store level
ISINSCOPE('T2'[storename]), "Checked",
-- At the Area level, count distinct store names

ISINSCOPE('T1'[area]), storeCount,
-- At the City level, sum distinct store counts from all areas under that city
ISINSCOPE('T1'[city]),
SUMX( VALUES('T1'[area]),
CALCULATE(DISTINCTCOUNT('T2'[storename])) ),
-- At the State level, sum distinct store counts from all cities under that state
ISINSCOPE('T1'[state]),
SUMX( VALUES('T1'[city]),
CALCULATE(DISTINCTCOUNT('T2'[storename])) ),
-- At the Country level, sum distinct store counts from all states under that country ISINSCOPE('T1'[country]),
SUMX( VALUES('T1'[state]),
CALCULATE(DISTINCTCOUNT('T2'[storename])) ),
-- At the Proc level, sum distinct stores across all hierarchy levels
ISINSCOPE('T2'[Proc]), storeCount,
-- Default case

BLANK()
)

I had tried with this measure as you can check below where BS Disp_Corrected is the measure you have given and BS_Disp_Corrected_1 is the measure that i have mentioned above so now you can observe that for country US total products are 8190

At State level for Alabama we have 61 products in total


At city level for Alabama State we have 6 products for Auburn city and the list of 6 products is provided at next level hierarchy.

I hope this will work...let me know 😊

[v-aatheeque]

Hi @modnacc ,

 If our response addressed by the @Preeti_G   community member for  your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @modnacc 

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[v-aatheeque]

Hi @modnacc ,

 If our response addressed by the @Preeti_G   community member for  your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community Forum!