How to Dynamically Filter Table B Based on Parameters in Table A Using DAX?

[ashi7upt]

Here’s a reframed version of your question:

---

Hi everyone,

I have two tables, A and B, and I'm looking to filter table B based on the information in table A.

**Table A**:

| parameter | value |
| --------- | ----- |
| a | x |
| a | y |
| a | z |
| b | u |
| b | v |
| b | w |
| c | p |
| c | q |
| c | r |
| c | s |

**Table B**:

| id | a | b | c | and more |
|----|---|---|---|----------|
| 1 | x | u | p | |
| 2 | x | u | q | |
| 3 | y | v | r | |
| 4 | y | w | m | |
| 5 | z | w | n | |

In Table A, the `parameter` column lists the names of the columns in Table B that should be used for filtering, along with their corresponding `value`. As Table A can be updated with more columns from Table B, is it possible to create a dynamic query that filters Table B based on the columns specified in Table A?
Is it possible DAX.

Thanks in advance!

[Anonymous]

Thanks for the reply from rajendraongole1 and asadmd93.

Hi @ashi7upt  , 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution or or mark the helpful replies, it will be helpful for other members of the community who have similar problems as yours to solve it faster . Thank you very much for your kind cooperation!

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[ashi7upt]

Hi @Anonymous 

The issue is not solved.
I have tried it but this method doesn't help a lot.
The biggest issue with this approach is filters are coming from the user side and for every user access level they have different filters.
for example for user X parameter in table A has 3 values but for user Y it has 6 values and they are not fixed these can change as and when the user wants.

[asadmd93]

To make the filtering dynamic based on the combinations in Table C, you can modify the DAX measure to include the combinations from Table C

FilteredTableB =
VAR DistinctParameters = VALUES(TableA[parameter])
VAR FilteredB =
FILTER(
TableB,
COUNTROWS(
FILTER(
TableA,
SWITCH(
TRUE(),
TableA[parameter] = "a" && TableB[a] = TableA[value] && TableA[parameter] IN VALUES(TableC[parameter]), TRUE,
TableA[parameter] = "b" && TableB[b] = TableA[value] && TableA[parameter] IN VALUES(TableC[parameter]), TRUE,
TableA[parameter] = "c" && TableB[c] = TableA[value] && TableA[parameter] IN VALUES(TableC[parameter]), TRUE,
FALSE
)
)
) = COUNTROWS(DistinctParameters)
)
RETURN
FilteredB

This modification ensures that the filtering conditions are dynamically applied based on the combinations stored in Table C.

If my post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are always appreciated.

[ashi7upt]

It does help me resolve the first issue but does not address the second one.

[asadmd93]

FilteredTableB =
VAR DistinctParameters = VALUES(TableA[parameter])
VAR FilterConditions =
ADDCOLUMNS(
DistinctParameters,
"Condition",
SWITCH(
TRUE(),
TableA[parameter] = "a", TableB[a] = TableA[value],
TableA[parameter] = "b", TableB[b] = TableA[value],
TableA[parameter] = "c", TableB[c] = TableA[value],
FALSE
)
)
VAR FilteredB =
FILTER(
TableB,
COUNTROWS(
FILTER(
FilterConditions,
[Condition]
)
) = COUNTROWS(DistinctParameters)
)
RETURN
FilteredB

DistinctParameters: This variable gets the unique parameters from Table A.

FilterConditions: This variable creates a table where each row contains a condition based on the parameter. For example, if the parameter is “a”, it checks if TableB[a] equals the value in Table A.

FilteredB: This variable filters Table B by applying the conditions. 

Let me know if that works. 

If my post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are always appreciated.

[rajendraongole1]

Hi @ashi7upt - You can create a new calculated table in Power BI that filters Table B based on the criteria specified in Table A.

FilteredTableB =
FILTER(
TableB,
VAR FilteredA =
CALCULATETABLE(
TableA,
TableA[parameter] = "a" && TableB[a] = TableA[value] ||
TableA[parameter] = "b" && TableB[b] = TableA[value] ||
TableA[parameter] = "c" && TableB[c] = TableA[value]
)
RETURN
COUNTROWS(FilteredA) = 3
)

Hope it works

[ashi7upt]

Hi @rajendraongole1 

Yes, we can do this way but I have two concerns 

 1. Filters in visuals are populated from a third table C values to Table A
     Table C stored the combination of parameters to be applied.

  2. In TableA[parameter] = "a" && TableB[a] = TableA[value]
     part of DAX TableA[parameter] = "a" will be written dynamically for every distinct parameter entry in 
table A

How can we address both or any of these issues?


Regards