Filtering table 2 without relationship

[ArslanManzoor]

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

[AntonioM · Accepted solution]

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