Slicing multiple fact tables with shared attributes

I am a beginner at Power BI and PowerQuery and have a question regarding dimension tables.

I have a report summarising testing results across several different test systems for a product. Each test is done on a certain component of each product, so has attributes structured like so:

• Batch 1
  • Sample 1
    • Component A
    • Component B...

And here are two examples of the fact tables containing test results.

ELECTRICAL TEST

MECHANICAL TEST

I want to have slicers that can filter all charts by batch ID, sample ID and component ID. So far I have been creating separate dimension tables for each of these, and creating a one to many relationship with the fact tables, but this results in very short single column tables e.g.

COMPONENT IDS

Is this the correct approach or should I be using a many to many relationship or some other method?

Your solution works perfectly and is part of PBI best practicies (Star Schema). In your situation you only have 3 variables in your Fact tables, so creating 3 Dim tables is easy, but if you have a lot of variables it can be more complicated. I will present you one strategy based on your example but this can be extended to any case in the same context.

You can follow these steps to accelerate the process and getting only one Dim table:

• Create Key columns in your Fact tables, these keys can be the concatenation of the 3 columns using DAX (like in my example) or any unique identifier for those 3 columns:

in ELECTRICAL TEST table add the following DAX column:

Key = 'ELECTRICAL TEST'[Batch ID] & 'ELECTRICAL TEST'[Sample ID] & 'ELECTRICAL TEST'[Component ID]

& in MECHANICAL TEST table add the following DAX column:

Key = 'MECHANICAL TEST'[Batch ID] & 'MECHANICAL TEST'[Sample ID] & 'MECHANICAL TEST'[Component ID]

• Now you can create DimTEST table containing the desired filter columns and the same key column, you can use DAX to create the table as follow:

DimTEST = SUMMARIZE(
                    UNION(
                          SUMMARIZE('ELECTRICAL TEST',
                                    'ELECTRICAL TEST'[Batch ID],
                                    'ELECTRICAL TEST'[Sample ID],
                                    'ELECTRICAL TEST'[Component ID],
                                    'ELECTRICAL TEST'[Key]),
                          SUMMARIZE('MECHANICAL TEST',
                                    'MECHANICAL TEST'[Batch ID],
                                    'MECHANICAL TEST'[Sample ID],
                                    'MECHANICAL TEST'[Component ID],
                                    'MECHANICAL TEST'[Key])
                         ),
                    [Batch ID],
                    [Sample ID],
                    [Component ID],
                    [Key]
                    ) 

What we are doing here is just grouping the Fact tables by filters and keys, unioning them and then grouping the result by filters and key to avoid duplicates, getting the desired DimTEST table.

• The last step is to define 2 relationships:

  • A 1<=>* one way filtering and active relationship between DimTEST table and ELECTRICAL TEST table

  • A 1<=>* one way filtering and active relationship between DimTEST table and MECHANICAL TEST table

You can now use this DimTEST table to filter the visuals as desired.

The following part is only my interpretation of this method, It would be great if other PBI users can add more information and their thoughts on this methodology. Also you will need to test the performance (and eventually the loss of performance) compared to the classical Star Schema.

PROS:

• It gives an automated solution if you have the same situation with more columns, the creation of the Key tables in the Fact tables can be done quickly with an advanced text editor

• This method allow you to have a unique filter with hierarchical levels, to so you will need to create a slicer containing the 3 columns DimTEST[Batch ID], DimTEST[Sample ID] and DIMTEST[Component ID]

• If you choose to display the 3 filters separately they will be dynamic, meaning that filtering by a Bactch ID will impact the 2 others filters and vice versa (this will also be part of CONS since sometimes it is not desired but there is a workaround)

• It simplifies the data model having only 1 Dim table compared to n, n being the number of filters you need to have

CONS:

• You are not exactly using the Star Schema (even if this is a Dim table defines another Star Schema)

• You will maybe loss some performances, especially if the keys goes big

• Your Dim table will grow in size (rows & columns) when adding more filters

• If you choose to display the 3 filters separately they will be dynamic, meaning that filtering by a Bactch ID will impact the 2 others filters and vice versa. To avoid this behaviour, go to the report view => select one of your filters => go to Format tab in the top => click on modify interactions => hover over all the other filters and click to disable cross filtering. You can do that for all your filters or choose the ones that still need to have cross filtering applied let them dynamic.