topic Re: Dynamic Table Columns With Slicer Using Field Parameters in DAX Commands and Tips

Dynamic Table Columns With Slicer Using Field Parameters

Anonymous — Sun, 16 Oct 2022 00:49:57 GMT

Hi,

I have a table visual that is controlled by slicer and both table & slicer are using field parameters. It’s working fine but I would like to modify the slicer. The slicer currently has 9 values:

Kiosk Revenue
Kiosk Comparative
Kiosk Variance
TEX Revenue
TEX Comparative
TEX Variance
Online Revenue
Online Revenue Comparative
Online Revenue Variance

I would like to reduce it by grouping the values into 3 categories:

Kiosk
TEX
Online Revenue

So based on the above changes, the operation will be the same i.e.

when “Kiosk” is selected, the columns “Kiosk Revenue”, “Kiosk Comparative”, and “Kiosk Variance” will show on the table
when “TEX” is selected, the columns “TEX Revenue”, “TEX Comparative”, and “TEX Variance” will show on the table
when “Online Revenue” is selected, the columns “Online Revenue”, “Online Revenue Comparative”, and “Online Revenue Variance” will show on the table
when no values selected, all columns will show on the table

Is this possible to achieve? Hope that makes sense. d_gosbell appreciate any help please.

Pbix file for reference
https://drive.google.com/file/d/1EEW1KK33DArAHRH34VqZNjNk6KqJPaFM/view?usp=sharing

Short clip of the issue

https://www.awesomescreenshot.com/video/11722864?key=8ccea0351155f9c35870e85bd501f45b

Re: Dynamic Table Columns With Slicer Using Field Parameters

Shaurya — Sun, 16 Oct 2022 07:17:10 GMT

Hi Anonymous,

Create a new category column that has values Kiosk, TEX and Online Revenue and use that for your slicer. Check the measure created for your field parameter and modify it accordingly.

Did I answer your question? Mark this post as a solution if I did!
Consider taking a look at my blog:

Re: Dynamic Table Columns With Slicer Using Field Parameters

d_gosbell — Sun, 16 Oct 2022 08:03:51 GMT

I don't think you even need a field parameter to solve this.

If you create a table with your 3 values (I just called it category)

Category = DATATABLE("Category", STRING, {{"Kiosk"},{"TEX"},{"Online"}})

Then as Shaurya suggested you can create measures checking for the selected value in the slicer (I would make the slicer a single selection). This is an example revenue measure, you would repeat the same pattern for Comparative and Variance.

Revenue = SWITCH( SELECTEDVALUE(Category[Category]),

"Kiosk", 'Vend_Sale'[Revenue SUMX],

"TEX",'mm_sheets'[Current Year Pax SUMX],

"Online", 'Vend_Sale'[Transaction Count SUMX],

blank())

Re: Dynamic Table Columns With Slicer Using Field Parameters

Anonymous — Sun, 16 Oct 2022 08:42:05 GMT

Hi d_gosbell and Shaurya , thanks for the feedback. That works but this is not the result I'm looking for. I need the columns to appear when value in slicer is selected and if no values selected, then columns are hidden. Hence the field paramter method I chose.

Can I use integrate your method using the field parameters? I'll have a go but please do let me know if you find a quicker method. Thanks.

Re: Dynamic Table Columns With Slicer Using Field Parameters

d_gosbell — Sun, 16 Oct 2022 08:49:30 GMT

Anonymous wrote:

Hi d_gosbell and Shaurya , thanks for the feedback. That works but this is not the result I'm looking for. I need the columns to appear when value in slicer is selected and if no values selected, then columns are hidden.

This is my solution working the way you've described. I've put a revenue and comparative measure in the values and created a slicer for the category

Re: Dynamic Table Columns With Slicer Using Field Parameters

Anonymous — Sun, 16 Oct 2022 09:07:56 GMT

d_gosbell , don't know what I did wrong the first time, but it's working for me now...without the need for field parameters.

Many thanks for your help and patience. Appreicate it.