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

Question

Hey PowerBI&nbsp;Rockstars!&nbsp;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!&nbsp;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?&nbsp;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!&nbsp;

vanessafvg · Answer

&nbsp;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?&nbsp; you need to give more information, this is sort of conceptual but without understanding the structure of your&nbsp; model its hard to advise on the situation

mpcosmin · Answer

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

mpcosmin · Answer

Thought to redo the first picture:

vanessafvg · Answer

so what does your data model look like in dq?&nbsp; ie. are you pulling from two separate dimensions?&nbsp; maybe there is a dax measure you can&nbsp; 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&nbsp; inbetween to reduce what you bringing back in the slicer.&nbsp;&nbsp;
&nbsp;
sometimes a use case calls for unconvential methods, maybe you could create a table with distinct values of that plant / station&nbsp; combination using a snow flake?&nbsp; Or maybe its even worth denormalising the tables if the performance and user experience is that unpleasant?
&nbsp;
been a long week so my brain might not be working.&nbsp;
&nbsp;
there are also ways of optimising your back end depend on what db you are using.&nbsp;

mpcosmin · Answer

Sorry for the delayed response. I use only 1 Data source.&nbsp;The other lists are just the Slicers that have parameters bound to them and the parameters are injected in the original SQL.

Forum Discussion

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

8 Replies

Related content

Parameter in summarize

Parameters to fabric pipeline

notebook parameter

Parameter selection for charts

Format parameters in Feld parameter