Only load referenced data from dataflow

[T_Deufel]

Hello! 🙂

i have a hard time understanding filtering options of dataflows. Given the following example:

Table1 (Fact table which stores the movement of certain parts) :

PartId	ProductionStep	Date
1	Step A	21.03.2024
2	Step A	21.03.2024
3	Step A	21.03.2024

Table2 (Dim table which stores master data of all produced parts with > 2 million rows):

PartId	Data
1	Lorem Ipsum
2	Lorem Ipsum
3	Lorem Ipsum
1992924	Lorem Ipsum

I can now filter Table1 to my desires, e.g. ProductionStep = Step A and Date = 21.03.2024. In the dashboard, i now want to show the masterdata from Table2. To achieve this, i can build a relationship between Table1 and Table2 based on the PartId column. 

The problem i am facing now is, that i always have to load the complete Table2, even though i only would need that PartIds which exist in Table1. 

Is there a solution to omit the complete load of Table2?

In a setting with an SQL Server, i could achieve this either by joining the Table1 and Table2 beforehand or by using a CTE and filter both Tables using the CTE. I cant think of an easy way doing this with a Dataflow, without alterting the dataflow itself.

Many thanks for your input, Timo

[DataInsights · Accepted solution]

@T_Deufel,

Three ideas:

1. Modify the dataflow to use custom SQL that performs the filter.

2. Modify the dataflow by creating an inner join between Table1 and Table2.

3. In the pbix, use Power Query to create an inner join between Table1 and Table2 (Merge Queries). This will leave your dataflow intact, which may be preferred if any downstream objects need the full Table2.