Forum Discussion
Filtered Table filtering other tables in visualization
Hi everyone
After a week of trial and error, and with the support of MFelix I was able to find a sollution to my problem.
What I did:
I duplicated my original table three times. Merged these tales with the three other tables I wanted to filter. Then I seperately created a measure for all these tables, and used this measure as a filter in these tables.
Thats how I was able to achieve the results I wanted
Thanks for reading!
Hi Anonymous ,
Can you please elaborate a little bit more and what you want to achieve?
To what I can understand you are trying to select certain values from a list (a calculated table on DAX) and then find the related values on 3 other tables?
Do you want to have this by user selection or by all the values on your table?
One option can be create a LOOKUP formula that returns all the values that matches the values.
Can you share a sample and expected result please?
Thanks for your reply
Ill try to visualize what I want:
DAX Table (filtered by slicers and filters)
Index Article Description Level
23 123-yB description 2
24 456-98 description 3
25 56GB-1 description 3
...
Stock Table
Article Description ...
123-yB tzja
456-98 hjasdkl
56GB-1 hjasdf
...
Delivery Table
Article ...
123-yB
456-98
56GB-1
Production Table
Article ...
123-yB
456-98
56GB-1
All tables contain the Article column. I want to get the list of articles from the filtered DAX table and use them to filter the other three tables. (I already have a relationship between the tables)
thanks again
Hi Anonymous ,
If you already have the relationship between the 4 table what you need to be sure is that the DAX table is the common base between all other tables and use the Article from that table on the visuals you are using and/or measures.
I assume that the relationship is one to many with the other tables.
However what I'm not understanding on your question is this paragraph
"In some cases there could be a couple hundred values, and control-clicking would not be an option. (Control-clicking and selecting multiple values is the only sollution I found until now)"
What do you mean by the filtering how do you want to have this filtering done without the ctrl click? one option can be placing article on a slicer that allows you to have a check box without the use of control.
Sorry for making this question but not really grasping what is the problem you are getting.
Hi MFelix
Between my tables there is a many to many relationship with one direction (DAX Table controlling the others) The reason of many to many is because none of the tables have ony distinct values.
i will try to explain the "Control-Click thing" again 🙂
If i click on one of the records of the DAX table, then the other three tables are filtered according to the selected Article. => if I clicked on the article 123-xyz then this article is searched in the other lists and if its found then a record returned, if not it stays blank. That is good, But the problem is I would like to search all shown articles from the DAX table in the other 3 tables.
i hope its more understandable
What I maybe should mention, is that I created a relatively complicated measure which gives me an "x" if true, and blank if not. Then I use this measure to filter my DAX table.
I also added the article from the DAX table to the other tables, but nothing happened. Then I used this meauser as a filter, selected show only "x" and then no records are shown.
if my case is still not clear, I can try to send some of my DAX code and maybe screenshots.
(What I'm doing, is creating a Bill Of Materials list. There is a search function where we can search for a specified article at any level. In my DAX Table I get this article and all the other "under" articles which make up this article. Logic behind: find searched article, tell me which level it is on (for example 2). Give me the index. Search for the next level which is equal or smaller then the level of the searched article starting after the given index. then give me the index of this next level. all the values between these two index values are to be show. and these shown values I want to use to filter the other 3 tables.)
Thanks a LOT for your time!
Daniel
Hi Anonymous ,
Having many to many relationships increases your problems for getting correct results, best options is to have a dimension table with unique values to link them all together.
I understand what you are trying to get but believe that you should use a parent child setup, similar to what is in this setup.
https://www.daxpatterns.com/parent-child-hierarchies/
However don't know you full model and maybe there can be some work around with a filter function as you have for the other table.
If you want to share data would be great. If sensitive do it trough private message.