Apply Report Page Filter to two Source Tables

[dwelsh]

There is probably a pretty easy answer to this question, but I cannot figure out how to do it or find a solution online.  I have two transaction tables that are related by a customer field and both have an identical store table linking into them.  The relationship between the transaction tables always has to be active on customers to get the correct results on Transaction2 table.

I want to have a filter on my Reports page for Store that would apply to both Store tables.    For example, if Chicago is select on a report page filter, a Chicago filter would apply to each Store table while still maintaining my customer relationship between the two transaction tables.   Is there a way to do this?

I have tried relating a single store table to both Transaction tables but can not make it active since the transaction tables are already linked.   Same for trying to link Store to Store2.  I have also tried making a calculated column of a concatenation of the customer and store field on the Transaction table.  This works fine if only a single store is selected, however if multiple or all stores are selected and a customer only has a transaction in 1 store on the Transaction table and 1+ on the Transaction2 table, I will only get their sales for the 1 store for that customer.

[v-shex-msft]

Hi @dwelsh ,

I'd like to suggest you extract correspond id from two transaction tables and use new table as a bridge table to link to transaction tables. 
Then you can use the bridge table field as source of filter to apply filter effect on both two transaction tables.

Regards,

Xiaoxin Sheng

[dwelsh]

Thanks @v-shex-msft.  I am not sure this will work.   For the Transaction table, I am applying a filter from another relationship I didn't include in my screenshot.  The other relationship is for Discounts.  When a specific discount is selected in my Report Filter, I want the Transaction table to give me a list of all distinct customers who used the transaction and use that to filter the Transaction2 table to see total customer sales.

If a create another transaction table, and use that as a bridge,  won't I still have the same problem where I cannot have two relationships (Transaction customer key and brigde transaction key) pushing to the Transaction2 table?   Or I am misunderstating what you are suggesting?

[v-shex-msft]

Hi @dwelsh ,

I mean you can break the relationship between transaction tables and create a calculate table with keys extract from stores. Then link stores with bridge table instead of direct links between transaction tables.

Notice: keep one major relationship instead of link all tables with different relationship keys, they may cause the issue when analysis records based on relationships.

What is the Direction of Relationship in Power BI? 

Create and manage relationships in Power BI Desktop 

Relationships in tabular models 
Regards.

Xiaoxin Sheng

[dwelsh · Accepted solution]

The links above didn't help me figure out how to filter Stores2 from a Store Filter.  I eventually used the below Measure to meet my needs:

FilterSelection = IF(LASTNONBLANK('Stores2'[npcsto_pk],"") in VALUES('Stores'[npcsto_pk]),1,2)

Then on any visual I wanted the Stores filter to apply to the Stores2 table or the tables it was related to, I would add the measure as a filter and set the filter to 1.   It works, but it does require me to enter the FilterSelection measure into every visual's filters since I cannot apply it to Page level or All pages.   I am accepting this as the solution, however if others know of a better way to do this, let me know.