Same filters for different Tables - Relationships and Crossfilter

[JCPO]

Hi Everyone,

I'm having a problem on relationships and crossfiltering for my report.
I have two tables containing 6 similar columns: Area, Customer, Material, Week, Category, Risk Type - then I have different risk values for each tables.

What I have - different files coming from different sources/excel files.

Table A: Area, Customer, Material, Week, Category, Risk Type, X Risk Value
Table B: Area, Customer, Material, Week, Category, Risk Type, Y Risk Value

What I want to do:

1. Combine the values in both tables in a single visual using any of the columns mentioned above.
2. Implement a "Master filter" for both table. E.g. Creating a dropdown based on "Week" column then be able to use that as a filter for each values (separate visual). Be able to do the same for Category, Area, etc. 
3. Be able to scale up the relationships and connection to be able to add new excel files in the future.

Hope you can help!
Thanks - J

[jdbuchanan71]

Hello @JCPO 

In the future, when you added new excel files, will they be in the same format?  It sounds like you have files coming from different departments or regions in your company but each department sends same data and you could be getting files from new departments in the future.  Is that the case?

Are those the only fields in your tables?  Is there some kind of identifier of which department sent the data or do you need one?

I'm thinking it might work for you to stack the data into a single large table in PowerQuery but let me know if misunderstood the flow of your data.

[JCPO]

Hi @jdbuchanan71,

Yes, I can identify different data sources from each departments and would be able to distinguish it. To give more clarity on the nature of the data: 

Table A: Good stocks in distributors

Table B: Bad Stocks in distributors

Table C (future): Total internal company inventory. 

What I would like to get is the risk value in each table based on ruleset that I have created. Then from there - I just want to use a single filter to show it on a per category, area, week, etc. I tried two things: 1) Creating a bridge table for Table A and B - I hit a dead end since I can only create a single connection still even for a bridge table and 2) Combining Table A and B - also didn't work, but probability of me doing it incorrectly is high. I just experimented on append and merge queries. 

Hope this clarifies!

[jdbuchanan71 · Accepted solution]

You can create lookup tables from your source tables that you then link back to your Table A and Table B.  Then you use the lookup tables as the filters over your main detail tables.  It looks something like this

And the model is like this:

The tables are easy to create and will stay up to date if new items get added.  The area table for example is simply.

Areas = 
DISTINCT(
    UNION(
        DISTINCT('Table A'[Area]),
        DISTINCT('Table B'[Area])
    )
)

If you add a new table in the future you would just update the code for each lookup table to include the new source. 

Areas = 
DISTINCT(
    UNION(
        DISTINCT('Table A'[Area]),
        DISTINCT('Table B'[Area]),
        DISTINCT('Table C'[Area])
    )
)

Then link the new table into each of the lookups and add the values into your measures.

If there are fields that always go together, say customer and area (meaning the same customer will always be in the same area) you can have those combined in one table then you would just join the Customer field into table A and B

Customers 2 = 
DISTINCT (
    UNION (
            SUMMARIZECOLUMNS('Table A'[Customer],'Table A'[Area]),
            SUMMARIZECOLUMNS('Table B'[Customer],'Table B'[Area])
    )
)

I have attached my sample file for you to take a look at.

[JCPO]

@jdbuchanan71 will definitely try this! I pictured this solution in my mind but I wondered if this is the most efficient one. Will let you know of the result!

Thanks!

[JCPO]

@jdbuchanan71 works like a charm! Thank you!