Latest Enterprise DNA Initiatives

How to make the different matrices dynamic?

I created two matrices, one contains the main accounts and the other contains the most important details (it’s a sort of explicative matrix). You should know that the elements of the details accounts are not in the same level : other goods are in a higher level than gaming, security…that’s why I cannot drop the columns from my fact table and that’s why I created measures for each element and used the switch function. Another thing to know is that my original data is huge, and I can’t modify column from the source or anything. So I created a sample of my data and shared it in the following PBIX file. The result you see here is what I’m looking for. But I have a problem, my two matrices are not dynamic, when I select an element in one of them it doesn’t filter the other table. I tried to create a relationship between the fact table and PLMOD, but when I do that, the elements with no code (such as operational income ) disappear from my view or displays a blank value even when I used the switch formula. Is there any DAX or manipulation I can use to make the two matrices dynamic and of course keeping the order I displayed ? Thanks for your help.

Matrices relationship issue-1.pbix (137.8 KB)

Hello @chaouiya

so the fact that you do not have a relationship between plmod and sales detail table tying back to the fact table is the problem. did you try to establish a relationship between placcount and plmod?

also what is the link btween the sales details and the fact table?

I tried to create the relationship between PLMOD and the fact table, but when I do so, the elements with no account code (operational income for example) don’t display in my matrix visual. I created a relationship between plmod and PL accounts but no effect. For the Detail account, they are within the fact table, it’s like an inferior level of what’s displayed in the main accounts. The role of this detail is to explain the amounts of the first matrix. I have chosen those elements because they’ve a significant impact on the first matrix (in terms of explanation).

I will let some of the experts chime in but my thought is that you need to look at your data model. In its current state - unless you can define a better star relationship - it is going to be a constant challenge to maintain the report. Sorry if i am stating the obvious. can you check your model in the database and see if it offers any clue on how you can better define the relationship?

Thanks for your answers. I tried but I couldn’t find a way to solve this problem.

Hi @Chaouiya, We’ve noticed that you created a new thread with the same question. We are closing this thread and tagging it as Solved. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum How To Use The Enterprise DNA Support Forum. Thanks!