Forum Discussion
Filter and Visualize Historical Data Using Slicers
I have 3 tables:
- A Countries and Groups table that contains a list of countries and the respective Groups they belong to. This table has countries that can have duplicate or triplicate entries due to a change in Group at a specific point in time. For example, Spain is listed twice because from 01/2000 to 11/2019 they belonged to Group A, and from 12/2019 to NOW they belong to Group B.
- A Metrics/Data table containing measures and dimensions that I am reporting and creating visualizations for.
- A standard Date table.
- 1 is related to 2 by Country Code
- 2 is related to 3 by Date
I currently have slicers in place for Country, Group and Date. What I want to do is to have my date slicer filter out Group B when I select dates from 01/2000 to 11/2019 and then filter out Group A when I select dates from 12/2019 and beyond all while showing Spain and their sales, revenue and other metrics.
As an added bonus, it would be great to be able to report Group metrics based on the above. For example, if the sum sales of Group A includes Spain from 01/2000 to 11/2019, it shouldn't include Spain from 12/2019 and beyond.
Any help would be greatly appreciated.
5 Replies
jytech - You will probably want to use the Disconnected Table Trick. Basically, you form a relationship between two disconnected tables using a measure. In this way, you can be very exact with regard to how the tables relate to one another.
Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563
Here are additional examples:
Greg_Deckler I appreciate your response but I'm not sure how the Disconnected Table Trick will help me. I posted a follow up to my original post which specifies exactly what I need with some images for reference.
HI jytech,
Please share some dummy data with the expected result to help us clarify your data structure and test coding formula on it.
How to Get Your Question Answered Quickly
Regards,Xiaoxin SHeng
Scenario 1:
- Spain is selected from the Country slicer.
- In the Group slicer (column in same table as Country), it displays Group A because before 11/30/2019 it was part of Group A.
- The Date slicer (DAX created calendar) shows 1/1/2018 - 11/30/2019 and all the data/metrics for that timeframe. For example: Users 1,408 and etc.
Scenario 2:
- Spain is again selected from the Country slicer.
- In the Group slicer (column in same table as Country), it displays Group B because after 11/30/2019 it is now part of Group B.
- The Date slicer (DAX created calendar) shows 1/1/2018 - 11/30/2019 and all the data/metrics for that timeframe that correspnd to the date range selected. For example: Users 930 and etc.
As mentioned in my original post, as an added bonus, it would be great to be able to report Group metrics based on the above. For example, if I select Group A my report will include Spain from 01/2000 to 11/2019, however shouldn't include Spain from 12/2019 and beyond because they are now part of Group B.
Does this make more sense? Can anyone help?
