Forum Discussion
Dynamic dimension in a matrix with slicer
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:
"
appreciate your help to rectify. tables are joined by store name, but they can also be joined by areas
Thanks
"
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 namesISINSCOPE('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 caseBLANK()
)
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 😊
4 Replies
- Preeti_GRegular Visitor
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 namesISINSCOPE('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 caseBLANK()
)
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-aatheequeCommunity Support
- v-aatheequeCommunity Support
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!