Forum Discussion

jps_HHH's avatar
jps_HHH
Icon for Helper II rankHelper II
1 year ago
Solved

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;...
Need Help
  • Deku's avatar
    Deku
    1 year ago

    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] )

    )

    )

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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.