Dynamic Parameters ( Multiple parameters that talk to themselves ) EG: Plant-Line-Material

[mpcosmin]

Hey PowerBI Rockstars! 
It's Cosmin, and guess what? My team and I are always pushing the envelope when it comes to Direct Query functionalities. One area that's been an interesting playground for us? Dynamic parameters in hierarchical granularities! 
Picture this: You've got hard-coded parameters in your SQL code—Plant Name, Business Area, Line Name—and you want these to play nice with each other. Choose a Plant Name, and voila! Your Business Area and Line Name parameters automatically shrink down to only relevant options. Sounds like a dream, right? 
Unlocking this capability is something we've been exploring and it's set to take our Direct Query and Big Data reporting game to elite levels. Have you ventured into this territory? Got some insights to share? Let's connect and raise the bar together! 

[vanessafvg]

 could you not just pass a plant name via a filter in power bi and use cte's in a view in sql to do the rest?  you need to give more information, this is sort of conceptual but without understanding the structure of your  model its hard to advise on the situation

[mpcosmin]

Thanks, Vanessa for your response. I will try to be more specific and will share some screenshots.

[mpcosmin]

Thought to redo the first picture:

[vanessafvg]

so what does your data model look like in dq?  ie. are you pulling from two separate dimensions?  maybe there is a dax measure you can  use a flag with isfiltered placed on the filter pane to get the correct scope and have some calculation logic in there that triggers the fact that understand the relationship  inbetween to reduce what you bringing back in the slicer.  

sometimes a use case calls for unconvential methods, maybe you could create a table with distinct values of that plant / station  combination using a snow flake?  Or maybe its even worth denormalising the tables if the performance and user experience is that unpleasant?

been a long week so my brain might not be working. 

there are also ways of optimising your back end depend on what db you are using. 

[mpcosmin]

I'm trying to find out basically if I can create a list with more than 1 column so that the customer can cascade the chosen filters. And then, inject them in the right places of the SQL.

[vanessafvg]

when you say a list? is there always a particular flow to it and so for example you have 4 filters?

user filters filter 1, that then filters 2-4 where 1 is related?  

or 

user filters filter 1, that filters filter 2, user selects from filter 2, that filters filter 3 etc?

[mpcosmin]

second option. The user filters Plant from a distinct list. The selects Station from a distinct list. Then select a start date and then an end date. All slicers get data from a unique list that has distinct values for all 4 lists. And the lists are created with "select distinct from "whatever tables I have with dimensions" " 

[mpcosmin]

Sorry for the delayed response. I use only 1 Data source. 

The other lists are just the Slicers that have parameters bound to them and the parameters are injected in the original SQL.