Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 select any month from the slicer the graph will show that month data only from Table 1 & Table 2.
Crux: need to put slicer for the data in columns.
Thanks
Solved! Go to Solution.
@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,
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
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
@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
Thanks for your help....It is working now.:smileyhappy 🙂
Hello,
If you create a relationship between the tables, filters will work on both.
Alternatively you can create a new date table and link them both to your new table.
Then you use that data for filtering purposes.
Use this link for more information about a date table:
https://kohera.be/nl/blog/business-intelligence-nl/how-to-create-a-date-table-in-power-bi-in-2-simpl...
Good luck
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |