topic Re: DAX Dynamic Column population based on slicer selection in DAX Commands and Tips

DAX Dynamic Column population based on slicer selection

ajohnso2 — Wed, 13 Mar 2024 19:20:34 GMT

Hi All,

Im looking for some advice and guidance with the below scenario. I would like to achieve this result without using Field Parameters if possible..?

Some background:

I have a hierarchy slicer in this example with 3 levels, with Region Manager being the top.

I would like to populate a table visual with a column relating to the slicer selection.

For example in the below - ex1 - Area Manager 1 is selected in the slicer, so the Dynamic Column should list all of the stores in that area, if more than 1 area manager is selected, those stores should also be listed.

In Example 2 - Region Manager 1 is selected, so the Dynamic Column should now be populated with the list of all of the Area Managers within.

Is this achievable?

Re: DAX Dynamic Column population based on slicer selection

lbendlin — Wed, 13 Mar 2024 21:41:14 GMT

Why the Field Parameters restriction? This is exactly what they were designed for.

One alternative is to enable "Personalize Visuals" and teach your users how to use it.

Re: DAX Dynamic Column population based on slicer selection

ajohnso2 — Wed, 13 Mar 2024 21:53:34 GMT

Hi Ibendlin,

Thanks for your reply.

The reason I dont want to use field parameters is it would make the report clunky, firstly having to select a slicer value from the hierarchy and additionally then selecting at which level the visual should then display.

I am not completely against it personally however I know my user base would find some issue with it.

Re: DAX Dynamic Column population based on slicer selection

lbendlin — Wed, 13 Mar 2024 22:10:07 GMT

I would give them a chance to test it before coming to conclusions. They may end up liking the flexibility.

Re: DAX Dynamic Column population based on slicer selection

v-yanimei-msft — Tue, 19 Mar 2024 02:52:52 GMT

Hi ajohnso2 ,

I see that the problem doesn't seem to be solved, so I'll give my reply. Hope it helps. Thank you for the detailed description. Based on your description, I think that the data cannot be displayed well. When you select Region Manager 1 and Region Manager 2, if only the next level of the list is displayed as shown in the picture, there will be an ambiguity between Area Manager 1 under Region Manager 1 and Area Manager 2 under Region Manager 2. I think it's the matrix that can show the data better, as shown in the following pictures. What data you want to see can be achieved by simply dropping down the matrix. To learn more about matrix, click Create a matrix visual in Power BI - Power BI | Microsoft Learn.

Finally, I attach my specific test table Table.

Best Regards,

Caroline Mei

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