slicer for multiple tables

[jps_HHH]

I have 3 different tables in the powerBI. However, there is also 3 columns that are common between the three tables.

Table 1:   column X ; P; N; Year; Site; Location 

Table 2:  column A; Z; Year; Site; Location

Table 3: column B; X; Year; Site; Location

The dimension of the tables are different betwenn them.

I would like to create 3 slicers: one for "year" that filters all tables; one for "Site", and the othter for "Location"

[danextian]

Hi @jps_HHH 

You will need to create separate dimension tables that will bridge those three tables for each of those dimension

mentioned above.

LocationTable =
DISTINCT ( // Ensure unique locations in the final table
    UNION ( // Combine locations from multiple tables
        SELECTCOLUMNS ( table1, "Location", table1[location column] ), // Extract "Location" column from table1
        SELECTCOLUMNS ( table2, "Location", table2[location column] ), // Extract "Location" column from table2
        SELECTCOLUMNS ( table3, "Location", table3[location column] )  // Extract "Location" column from table3
    )
)

Do the same for sites.

YearTable =
//adjust the range accordingly
GENERATESERIES ( 2020, 2025, 1 )

Create a one-to-many single direction relationship from these dimension tables to the fact tables and use them in slicers.

[Deku · Accepted solution]

If you create a dimensions for year, site and location and setup a 1 many relationship to your tables you can use the dimensions in the slicers.

If there are different locations between the tables, for example, you can use the following the make the dimension

Distinct(

Union(

Distinct( table1[location] ),

Distinct( table2[location] ),

Distinct( table3[location] )

)

)

[jps_HHH]

So, I have to create 3 new tables ? 

Table location:

Distinct(

Union(

Distinct( table1[location] ),

Distinct( table2[location] ),

Distinct( table3[location] )

)

)

Table site:

Distinct(

Union(

Distinct( table1[site] ),

Distinct( table2[site] ),

Distinct( table3[site] )

)

)

[Deku]

Yes