Distinct values from columns in multiple tables (with ability for bi-directional filtering)

[devitus]

Hi,

I'd like to ask for advise with regards to this setup.

I have a number of tables in a dataset that contain performance of different advertising campaign types split by date. In addition to metrics, these tables contain text values that represent various campaign settings, such as advertised product, targeting type, etc. The tables need to remain separate as - while the above image shows the same layout - they differ in terms of metrics they show and require different logic to be applied in Power Query. In addition to that, their sizes exceed 5MM rows each and I'll be using incremental refresh, so keeping them separate is the best option to minimize refresh times.

For reporting purposes, I need to be able to summarize performance by distinct text values contained in each table, as well as be able to filter data in both directions (from the collection of distinct values pulled from each table to the data contained in the tables, and backwards).

I have used DISTINCT and UNION functions alongside a FILTER to generate a calculated column while excluding blank values as so:

Distinct Text Values = 
    DISTINCT(
        UNION(
            FILTER(VALUES('Campaign Type 1 Table'[Text Values]), 'Campaign Type 1 Table'[Text Values] <> ""),
            FILTER(VALUES('Campaign Type 2 Table'[Text Values]), 'Campaign Type 2 Table'[Text Values] <> ""),
            FILTER(VALUES('Campaign Type 3 Table'[Text Values]), 'Campaign Type 3 Table'[Text Values] <> "")
            )
        )

And have tried setting up relationships as follows:

However, it would only allow me to create relationship with bi-directional filtering for one pair of tables, blocking bi-directional relationship on others with the following error:

[UPDATE]: Campaign tables also feature relationships to other tables (CRM, OMS, etc.), but all relationships are downstream with one-way filters directed towards the Campaign tables.

I'd like to kindly ask for your help. My question is two-fold:

- Is this the most efficient way of achieving my goal? And if so:

- How do I solve the inability to set up bi-directional filtering on all three relationships?

Thanks in advance 🙂

[Anonymous · Accepted solution]

I agree with Amit, and I also suggest to make it Uni-directional or combining the data into 1 table using Power Query.

Additionally you can create a dimension for Campaign, if not already done. That way you will have a good star schema and Filtering will be governed by Dimensions - making it a better data model.

Assuming you already have a Date table connected.

Why do you need bi-directional filter? 

[amitchandak]

@devitus , Bi-Directional relationship creates two-path/loop, when you have more than two tables. Better to make the 1-M , single directional  .

Also you can cosider appending tables if the have same column in power query