Forum Discussion
slicer for multiple tables
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"
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] )
)
)
4 Replies
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] )
)
)
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.
