Forum Discussion
Solved
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 ...
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-aatheeque
Community Support
Community SupportHi 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-aatheequeCommunity Support
Community Support