Check if any value in each row appears in SELECTEDVALUE row

[jjjppp]

Hello, all.

I had a student ask whether it was possible to filter a table visual for all rows that match any value in the row selected in another table visual. I said yes! and threw together this example (new link), but this requires the column names to be specified in the expression, like below:

Shared Value? = 
    IF(
        MIN('Customers Copy'[City]) = SELECTEDVALUE(Customers[City]) || MIN('Customers Copy'[State]) = SELECTEDVALUE(Customers[State]), // etc etc
        1, 
        0)

I'm wondering if it's feasible to do this dynamically, so that all columns of a table could be checked against all values associated with SELECTEDVALUE. Some sort of VALUES(ROW(...)) IN SELECTEDROW(...) if you will.

I know DAX doesn't support traditional loop structures, but I suppose this is a bigger question about dynamically referring to columns in DAX tables (if only we had some sort of equivalent to Expression.Evaluate !!)

.pbix file is linked above. Please note the calculated Customers Copy table (I'm also wondering if this is feasible without the copy table) and Shared Value? measure (used in a filter on the second table). Thanks for any ideas!

[Icey]

Hi @jjjppp ,

I had a student ask whether it was possible to filter a table visual for all rows that match any value in the row selected in another table visual. I said yes! and threw together this example, but this requires the column names to be specified in the expression, like below:

---

Power BI has a definition of "Visual interactions". It means visualizations on a report page can impact each other.

By default, visualizations on a report page can be used to cross-filter and cross-highlight the other visualizations on the page.

For example, in your scenario, when you filter table1, another table2 on the same page will only show datas meeting the filters from table1. Here is an sample:

If I misunderstand your requirements, please let me know.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jjjppp]

Hi @Icey,

Thanks for taking a look. The problem with the default visual interactions is that it only filters other visuals on the most granular field present--in this case, Customers[Full Name].

I want it to filter to show all records with any field matches to the selected value. So in this case, any other customers that have the same City, State, Full Name, OR Zip Code.

I was able to do that by using "OR" logic with several equality checks to SELECTEDVALUE, but I had to basically hard code the column names into the formula. I'd like to be able to do this dynamically for any pair of tables so that each column of Table A is checked against each column of Table B and returns all rows where there are any matches.

[Icey]

Hi @jjjppp ,

I have an idea that in your example the range of the three columns is gradually increasing. For example, State > City > Name. In other words, Expression1 = Expression2.

Expression1 =
MIN ( 'Customers Copy'[City] ) = SELECTEDVALUE ( Customers[City] )
    || MIN ( 'Customers Copy'[State] ) = SELECTEDVALUE ( Customers[State] )
    || MIN ( 'Customers Copy'[Full Name] ) = SELECTEDVALUE ( Customers[Full Name] )

Expression2 =
 MIN ( 'Customers Copy'[State] ) = SELECTEDVALUE ( Customers[State] )

In this particular scenario, you only need to use the column with the most coverage. Hope I made it clear.

And in other normal scenario, to my knowledge, there is no other simple method.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ValtteriN]

Hi,

Dynamic column references are a bit tricky. I would approach this by using e.g. INTERSECT + VALUES and using a filter table within variable to apply the filters dynamically. Due to information security reasons I can't access your file so I can't give a more specific suggestion, but maybe this idea can be of help.

[jjjppp]

Thanks for the suggestion. That sounds like it could be promising. I'm sorry, I did not share properly. Hopefully, this link will work (unless it's a security issue on your end too).

Otherwise, here's a schema of the tables in question. It's very simple mock data.

There is no relationship between the Customers table and its shadow because it isn't necessary for the example above. Hope this helps! Thank you!

(Let me also add that this is not a critical thing, but I'd love to find a good solution for this)