Dynamic Table Columns With Slicer Using Field Parameters

[Anonymous]

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

[d_gosbell]

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())

[Anonymous]

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. 

[d_gosbell · Accepted solution]

---

@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

[Anonymous]

@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.

[Shaurya]

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: Forecast Period - Previous Forecasts