Use multi-select date slicer on several date columns in matrix managed by field parameter slicer

[Anonymous]

Hi, not that it's critical but I'd be happy to find out... 

I have a fact table with three date columns. All connected to a date field (date itself is a key) in calendar table so 1 active and 2 inactive relationships. I managed to have only one of the 3 columns show up at the same time on a matrix visual by using a field parameters slicer. There is another slicer based upon a Y-Q-M hierarchy, which I want to use to filter matrix rows by selecting multiple periods, not mandatorily continuous. Could be Q1'23, May'23, and Q3'23 etc. 

Time slicer (obviously) only works for the active relationship, no matter which column is shown. Any ideas on how to make inactive relationships employed in the same slicer? 

Thank you!!

[AlexisOlson]

Let me see if I understand...

If May'23 is selected, you want to show the rows where any of Created/Needed/Deadline dates are in that month regardless of the field parameter choice?

[Anonymous]

Not really. I need to be able to use the date slicer to filter rows by the column currently selected/shown on matrix by the parameter slicer.

[AlexisOlson]

I'd recommend using a calculation group instead of a field parameter.

See this article: Using calculation groups to switch between dates - SQLBI

[Anonymous]

I might be wrong but since I'm not calculating anything, how USERELATIONSIP might even help me filter out rows with scalar values AND dates next to them? Using the example you pointed me to, what I want to accomplish is to show the Sales table rows the way they are but have the ability to filter rows by either Order Date or Delivery Date.

I don't care if both dates are columns in that matrix. Probably the field parameter trick I used has little to do with my objective and just created confusion. It helps visualization and saves me space needed for more column but that's it. 

[Greg_Deckler]

@Anonymous You probably need to create a Complex Selector measure where based on the slicer selection uses the correct relationship via USERELATIONSHIP. Hard to be more specific without sample data to recreate. The Complex Selector - Microsoft Fabric Community

[Anonymous]

This looks promising. Not sure if I'm smart enough to figure out how to apply it to my case but thank you!

[Anonymous]

Hi @Anonymous 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I did not solve my problem yet, did not have time to dig further.