Forum Discussion

ankitkagarwal88's avatar
ankitkagarwal88
Regular Visitor
8 years ago
Solved

Creating a Single Slicer for Multiple Column

Hi,   I have data in two different tables in two different file as mentioned below. Want to put one common slicer for both on the month to show the monthly variance for below two tables. If I selec...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    ankitkagarwal88,

    1. Unpivot your columns of Table 1 and Table 2 in Query Editor of Power BI Desktop.


    2. Rename Attribute column in Table 1 and Table 2 to Month.

    3. Create a new table using DAX below.
    Table = DISTINCT(SELECTCOLUMNS('Table 1',"Month", 'Table 1'[Month]))

    4. Create relationship using Month field among the three tables.


    5. Create slicer using the Month field of the new table, then you are able to use the slicer to filter your visuals.


    Regards,
    Lydia

ankitkagarwal88's avatar
ankitkagarwal88
Regular Visitor
8 years ago

Hi Lydia,

 

Thanks for your help!!

 

Can you please help me with the variance calcualtion between the tables.

 

Like (Value of table 1-value of table 2)/value of table 1

 

Thanks

Anonymous's avatar
Anonymous
Not applicable
8 years ago

ankitkagarwal88,

1. Merge the two tables in Query Editor useing Merge Queries->Merge as new functionality, then expand the value field in the Merged table.


2. Create relationship between the Table and Merged table using Month field, create a calculated column in the merged table using dax below.

Column = (Merge1[Table 1.Value]-Merge1[Value])/Merge1[Table 1.Value]


Regards,
Lydia