Forum Discussion
Filtering table 2 without relationship
Hi,
I have 2 tables, where table 1 contains material data, alongside 'Green zone' limits which differ based on column 2 (Results). Assuming I use these query tables as visuals in my dashboard, I would like these limits to be applied as filters on table 2 when the user selects a particular row in table 1.
Unfortunately I cannot create a relationship between the 2 tables as there is no column in common (unless I'm missing a trick here).
I'd appreciate any ideas, thanks in advance!
TABLE 1 (Sample, made-up data)
Material 1 ID | Result | Green zone lower limit | Green zone upper limit |
1 | 2.1 | 21 | 23 |
2 | 3.2 | 24 | 26 |
3 | 2.5 | 20 | 22 |
TABLE 2 (Sample, made-up data)
Material 2 ID | Result |
A | 22 |
B | 24 |
C | 21 |
D | 25 |
E | 27 |
F | 22 |
G | 23 |
Hi ArslanManzoor ,
One option is to use a measure to filter the second table, comparing the values of results with the selected limits from the first table.
Within Limit = IF ( SELECTEDVALUE('Table 2'[Results]) <= SELECTEDVALUE('Table 1'[Green zone upper limit]) && SELECTEDVALUE('Table 2'[Results]) >= SELECTEDVALUE('Table 1'[Green zone lower limit]) , 1, 0)This will give you
and
Then add a visual filter to only show the 1s
And now you have
Alternatively, you could use Power Query to create a list of allowed values (within the limits)
Add a custom column in Table 1
{[Green zone lower limit]..[Green zone upper limit]}Which gives you a list of the range, that you could then expand and link to Table 2 on Table 2[Result]
This might be many to many but you can set the filter direction to be single
Which would give you the same result
1 Reply
Hi ArslanManzoor ,
One option is to use a measure to filter the second table, comparing the values of results with the selected limits from the first table.
Within Limit = IF ( SELECTEDVALUE('Table 2'[Results]) <= SELECTEDVALUE('Table 1'[Green zone upper limit]) && SELECTEDVALUE('Table 2'[Results]) >= SELECTEDVALUE('Table 1'[Green zone lower limit]) , 1, 0)This will give you
and
Then add a visual filter to only show the 1s
And now you have
Alternatively, you could use Power Query to create a list of allowed values (within the limits)
Add a custom column in Table 1
{[Green zone lower limit]..[Green zone upper limit]}Which gives you a list of the range, that you could then expand and link to Table 2 on Table 2[Result]
This might be many to many but you can set the filter direction to be single
Which would give you the same result