Slicer with items common items across the two options

[KKhatlisi]

Hi There,

I have a table that I use for forecasting that I want to slice base on the selection of static v live.

I have the FF items (YTD, MTD, Stat BL, Stat PL, Live BL, Live PL) are in the item column of my data set.

For a live selection, I want to return (YTD, MTD, Live BL, Live PL) and for Static View (YTD, Stat BL, Stat PL)

I want my selection to only show live or static as the options. I have attached a pic of what I would live to see.

[BrianJ]

@KKhatlisi,

I recently worked on a nearly identical problem with another member. Please check out the thread below – I think it will get you what you need:

How to Change table visual via a slicer

@amruthdna2018, Here’s one way to do this: create a disconnected supporting table containing your desired slicer values, in this case Disconnected Employer Table = VALUES( Employee[Employer] ) create a measure like this: Invert Slicer = IF( MAX( Employees[Employer]) IN ALLSELECTED( ‘Disconnected Employer List’[Employer] ), 1, 0 ) In one copy of the visual, set your filter pane condition for that visual like this: [image] And for the other visual, set up the same way but with t…

• Brian

[KKhatlisi]

Hi Brian,

this is not quite what I wanted to do. I, however, found a video that is similar to what I want to achieve using a slicer to change measures but I don’t want to change a measure.

[BrianJ]

@KKhatlisi,

Okay, sorry that wasn’t what you needed. If you can please post your PBIX file, I’d be glad to work through a more specific solution if you still need assistance.

Thanks.

– Brian

[EnterpriseDNA]

Thanks for posting your question @KKhatlisi. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

[KKhatlisi]

Hidden Slicer Option.pbix (63.2 KB)

Hi Brian,

Attached is the file.

Regards,
K

[KKhatlisi]

Hidden Slicer Option.pbix (63.2 KB)

Hi I have the file attached.

Regards,
K

[BrianJ]

@KKhatlisi,

Thanks for posting the PBIX file – that definitely helped me understand the requirement better. After looking at this, I decided the best path was to address all the necessary calculations and transformations in Power Query. Here’s what I did:

1. Create a custom column in Data called Live or Static based on [ITEM] value, and arbitrarily assigned YTD as “Live”:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Live or Static", each if Text.StartsWith( [ITEM], "Stat") then "Static" else "Live")

2. Duplicated the Data table, filtered down to only the YTD records, changed “Live or Static” to “Static” using Replace Value and renamed the table “YTD Static”

3. Appended YTD Static to the original Data Table

4. In the visual, now set the slicer to the Live or Static column and turned on Single Select in the slicer options (otherwise, if no or both options are selected, the total will be wrong since YTD records now appear in the data twice).

And here’s how it looks in operation:

I hope this is helpful. Full solution file attached below.

• Brian
  eDNA Forum - Hidden Slicer Option solution.pbix (98.3 KB)

[EnterpriseDNA]

Hi @KKhatlisi, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!