Forum Discussion
Filter with value from another table
Hello, I have a problem with a filter.
There are two tables that aren't linked. Table 1 has a slice, and only one value remains. I want to filter Table 2 to this value from Table 1. Show me all values that are <= the value from Table 1.
I hope you can help me.
With normal filters, I can't just define the value itself, but I can't refer to another field.
Thanks, Stefan.
Hey stefantaust ,
Hi Stefan! You’re on the right track by wanting to filter one table based on a value from another. Since your tables aren't related and you want to apply a dynamic filter, here's how to solve it depending on the tool you're using. Below are approaches for Power BI and DAX, which is a common scenario for this type of problem.
Problem Summary
You have:
Table 1 with a slicer applied → only 1 value remains (e.g., a threshold).
Table 2 that you want to filter dynamically to show rows where value <= threshold from Table 1.
There is no relationship between Table 1 and Table 2.
Solution Using DAX (Disconnected Tables)
Create a Measure to Extract the Slicer Value
SelectedValueFromTable1 = SELECTEDVALUE('Table1'[YourColumn])Create a Measure to Filter Table 2
You cannot directly filter a table visual using a column expression like that, but you can use a measure to achieve it.ShowValue = VAR threshold = [SelectedValueFromTable1] RETURN IF(MAX('Table2'[YourValueColumn]) <= threshold, 1, 0)Apply Visual-Level Filter
Add the ShowValue measure to your visual.
Set a filter on it: ShowValue = 1.
This will dynamically show only the rows in Table 2 where values are less than or equal to the selected slicer value from Table 1.
When Using Power Query
If you are in Power Query (instead of visuals or measures), it’s trickier because dynamic interaction via slicers isn’t available. You'd need to:
Pull the selected value into a parameter.
Reference that parameter to filter the other table. But this won’t be interactive like slicers in reports.
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam
5 Replies
Hey stefantaust ,
Hi Stefan! You’re on the right track by wanting to filter one table based on a value from another. Since your tables aren't related and you want to apply a dynamic filter, here's how to solve it depending on the tool you're using. Below are approaches for Power BI and DAX, which is a common scenario for this type of problem.
Problem Summary
You have:
Table 1 with a slicer applied → only 1 value remains (e.g., a threshold).
Table 2 that you want to filter dynamically to show rows where value <= threshold from Table 1.
There is no relationship between Table 1 and Table 2.
Solution Using DAX (Disconnected Tables)
Create a Measure to Extract the Slicer Value
SelectedValueFromTable1 = SELECTEDVALUE('Table1'[YourColumn])Create a Measure to Filter Table 2
You cannot directly filter a table visual using a column expression like that, but you can use a measure to achieve it.ShowValue = VAR threshold = [SelectedValueFromTable1] RETURN IF(MAX('Table2'[YourValueColumn]) <= threshold, 1, 0)Apply Visual-Level Filter
Add the ShowValue measure to your visual.
Set a filter on it: ShowValue = 1.
This will dynamically show only the rows in Table 2 where values are less than or equal to the selected slicer value from Table 1.
When Using Power Query
If you are in Power Query (instead of visuals or measures), it’s trickier because dynamic interaction via slicers isn’t available. You'd need to:
Pull the selected value into a parameter.
Reference that parameter to filter the other table. But this won’t be interactive like slicers in reports.
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif AzamHello stefantaust
Try this
Step-by-Step Solution
Table1 has a slicer on [SelectedValue] (only one value selected).
Table2 has a column [TargetValue] you want to filter based on the slicer value.
Create a measure like this
SelectedValueFromTable1 =
MAX(Table1[SelectedValue])
Then, create a second measure for filtering Table2:
ShowFlag =
VAR SelectedVal = MAX(Table1[SelectedValue])
RETURN
IF(MAX(Table2[TargetValue]) <= SelectedVal, 1, 0)
Now, apply a visual-level filter (on your Table2 visual) using ShowFlag:
Filter: ShowFlag = 1
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
Hi stefantaust Yes, use a measure in Table 1 to get the selected value:
SelectedValue = SELECTEDVALUE('Table1'[YourColumn])Then, create a column/measure in Table 2:
IsLessOrEqual = IF('Table2'[YourColumn] <= [SelectedValue], 1, 0)Finally, filter Table 2 using IsLessOrEqual = 1.
i have in Table 2 (checkpoints) more results. thats a problem
SelectedValue = SELECTEDVALUE('SOMA Profile'[Status (Start)])IsLessOrEqual = IF(Checkpoints[Status] <= [SelectedValue], 1, 0) A single value cannot be determined for the Status column in the Checkpoints table. This can occur when a measure formula references a column with many values without specifying an aggregation such as MIN, MAX, COUNT, or SUM to obtain a single result.
Hello pankajnamekar25
thank you for your solution.
i changed:Auswahl_SOMA = MAX('SOMA Profile'[Status (Start)])ShowFlag = VAR SelectedVal = MAX([Auswahl_SOMA]) RETURN IF(MAX(Checkpoints[Status]) <= SelectedVal, 1, 0)but i have an Error
German: Die Funktion "MAX" akzeptiert als Argument Nr. 1 nur einen Spaltenverweis. English: The "MAX" function accepts only a column reference as argument no. 1.
