Forum Discussion
How to filter 2 visuals when you dont have a solid relationship between 2 tables?
I have multiple tables one of them has the name of all the other tables and i want to make 2 visuals so that when i click on the name of the table i get one of the other tables with the same title
- Server Name | Size
- test 1 | 12mbs
- test 2 |96mbs
- test 3 |18mbs
- test 1 databases | size
- a | 6mbs
- b |6mbs
- test 2 databases |size
- a2 |90mbs
- b2 | 6lbs
- test 3 databases |size
- a3 |10mbs
- b3 |8mbs
This is an example of what i have.
I want to be able to have the server names on one chart and and when i click one of them i get the details on the same or different chart.
For example i click on test 1 and i get a1 and a2.
Is this possible?
i cant seem to figure out how am i supposed to do it or how can i make a relationsip between them.
Thank you for taking time to help me.
Hi undefined,
Assuming that you have tables of Test1, Test2 and Test3 DBs in separate queiries, i.e. The Tables of Test1 will be in a separate Query, Test2 in another Query and So on. you can achieve what you are looking for.
Use the below steps:
1. Create a new column in all the 3 queries of Test1, Test2 and Test3. This Column should have the Database Name in it.
2. All the Columns of the 3 Queries should have the same name
3. Append the 3 Queries together to for a single Query
4. Use the DBName column to a visual to fiter using it and the tables and size in a different visual.
Follow the screenshots below
Similar to Test1 also add DBName column to Test2 and Test3
After Appending the tables, the Query looks like
The Resultant Output
Append Option
Hope this is what you need!!!
If this fixes your need, Appreciate with a Kudo and accept as solution!!!!
5 Replies
Seward12533Solution Sage
Power BI works with structured data and your best bet is to have a single table like below and write a simple measure for Total Size = SUM(table1[Size]) and then use a Matrix to display the ressults then set up a slicer based for the user to display a test. This let let the DAX filter context handle all the heavy lifting for you. If you have a requjiredment to only show a single result then you can use an IF as a blocking function to only calculate if a single test is selected. I took it a step further and have dynamic message to prompt the user to select or provide a title for the visual.
The table on the left will show all the results if no test is selected but the table on the right will only show results if a single test is sselected with the slicer.
Here is a sample report - https://1drv.ms/u/s!AuCIkLeqFmlhhJg0x3prp6qlarKLWQ
Test Databae Size 1 a 6 1 b 6 2 a 90 2 b 6 3 a 10 3 a 8 - Thejeswar
Super User
Hi undefined,
Assuming that you have tables of Test1, Test2 and Test3 DBs in separate queiries, i.e. The Tables of Test1 will be in a separate Query, Test2 in another Query and So on. you can achieve what you are looking for.
Use the below steps:
1. Create a new column in all the 3 queries of Test1, Test2 and Test3. This Column should have the Database Name in it.
2. All the Columns of the 3 Queries should have the same name
3. Append the 3 Queries together to for a single Query
4. Use the DBName column to a visual to fiter using it and the tables and size in a different visual.
Follow the screenshots below
Similar to Test1 also add DBName column to Test2 and Test3
After Appending the tables, the Query looks like
The Resultant Output
Append Option
Hope this is what you need!!!
If this fixes your need, Appreciate with a Kudo and accept as solution!!!!
Thank you it worked but do you know how can i get values to not populate the graph until clicked on?
- Seward12533
In my sample this is exactly the behavior on the right hand visual. I used a blocking if function based on HASONEVALUE of TEST you would use the same measure or do sometihng simlar for whatever your plotting on your graph.