Changing rows in a dynamic table based on results and rules
I have a really (for me at least) tricky challenge in front of me. I have tried to describe everything on the PBIX it self, but will also upload the imaeg here:
Logic I want to implement:
1) In general I want to filter the tables based on the last 6 months (relative date)
2) IF filtered table (able to filter on Company_ID) returns "problems" of only 1 "Problem_Cat2_Text" - for example Company_Id = 1, THEN I want to show ALL problems for the same "Problem_Cat1_Text" but 2 years back!
3) I need this to by dymanic and able to handle new rows every single day.
4) Users might click on the table, hence the filter context changes. If possible I would like a solution where the filter context does not change based on user clicking on a row in the table. And a transparent shape, will NOT do the tricks since the user should be able to scroll up and down and siteways on the table, as more date gets added on.
Here are a couple of more images of the data model (simplified of course) and also the overall filter with 6 relative months, as it is set up as of now.
Please let me know if you have any questions, or if you do not think that this is possible at all? The original data source is from SSAS, Direct Query and using Power BI RS - Which means Field Parameters is NOT an option as of now. Not sure it would help anyway though. But thought I would try to recreate it in Power BI Desktop, so here you go.