Power BI Desktop filter table by slicer option and DAX switch

[bslintx]

I haven't delved into Power BI very long but finding it very frustrating as I am used to using a web frontend and sql backend.....the M/ DAX coding and measures are cryptic to me.....

The logic is the same but I have no idea how to apply:

I have a "parent" table that consists of multiple fields that I will want to filter on from a tiled slicer. These are "hidden" (in the visual but dragged out of sight) columns will be Yes/No values or derived from data within the table itself on-the-fly - see below

I assume these fields would have to be in the "visual table" for any filter to work even though the "source table" has it. I would rather not have to "hide" by sliding out of view and have to deal with borderless cell....but that is another topic lol

So, I thought this would be the (pseudo) logic:

1. Click a slicer "button"

[Bad Access]  [TOP 10 Hosts]  [TOP 10 Vulnerabilities]

2. Slicer value goes to a measure?                       

measure_slicer_bar = 



VAR slicervalue = get slicer value code here

VAR FilteredBadAccess = CALCULATETABLE( VALUES('Hosts'[Bad_Access]),FILTER(Hosts,Hosts[Bad_Access]="Yes"))
 
VAR FilteredTop10Hosts = TOP 10 Hosts Calculation here (derived from "total" column)

VAR FilteredTop10Vulnerabilities = TOP 10 Vulnerabilities Calculation here (derived from "VPH Avg" column)

VAR result = SWITCH (
                                     [slicervalue],
                                  "Bad Access",  FilteredBadAccess,
                                  "TOP 10 Hosts",  FilteredTop10Hosts,
                                   "TOP 10 Vulnerabilities", FilteredTop10Vulnerabilities
         )

RETURN result 

[sevenhills]

Button and Bookmarks method: If you are trying to do report bookmarks and hide the visuals based on the slicer. There are many ways. You can google Power BI Bookmarks and buttons. Lots of youtube videos too.

Slicer method: If you are trying to show the slicer using a table and refresh the calculated based on it. There are many way. I am providing one such way I did in the past.

1. Create a dummy table and make sure it is not connected to any table in the model.

2. Create the horizontal slicer with single select

3. Optional: Create one measure for one of each slicer value

4. Actual measure to switch:
Test first:

Sales By Slicer = 
var _SelSlicer = SELECTEDVALUE( 'Slicer Text'[Slicer Text] )

RETURN 

SWITCH( _SelSlicer, 
  "Bad Access" , "Bad Access: " & Round(Sales[Sales Amount by Due Date], 3) ,
  "Top 10 Hosts" , "Top 10 Hosts ... : " & Round(Sales[Sales Amount by Due Date] / 78 , 3),
  "Top 10 Vulnerabilities" , "Top 10 Vulnerabilities ... : " & Round(Sales[Sales Amount by Due Date] / 92.4, 3),
  BLANK() 
  )

 Replace with the actual calculation:

measure_slicer_bar = 



VAR slicervalue = SELECTEDVALUE( 'Slicer Text'[Slicer Text] ) -- get slicer value code here

VAR FilteredBadAccess = CALCULATETABLE( VALUES('Hosts'[Bad_Access]),FILTER(Hosts,Hosts[Bad_Access]="Yes"))
 
VAR FilteredTop10Hosts = TOP 10 Hosts Calculation here (derived from "total" column)

VAR FilteredTop10Vulnerabilities = TOP 10 Vulnerabilities Calculation here (derived from "VPH Avg" column)

VAR result = SWITCH (
                                     [slicervalue],
                                  "Bad Access",  FilteredBadAccess,
                                  "TOP 10 Hosts",  FilteredTop10Hosts,
                                   "TOP 10 Vulnerabilities", FilteredTop10Vulnerabilities
         )

RETURN result 

Hope it helps1

[bslintx]

Thanks for replying. 

I know how to do it with bookmarks but I want it cleaner and I need to try to get my hands a little dirty with the DAX. I believe I was in between 2 and 3 that you posted.

I have tried to reply mutiple times woth a picture but I am not seeing the posts?! Is there a time limit ....it's very strange that I can reply but get no message saying I have to wait....puzzling.

EDIT: I see it is up to 24 hours - yikes that is excessive ....will be applting some of your code to mine and will post results here - thanks!

[lbendlin]

I read your post a couple times but was unable to understand it.

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

[bslintx]

Appreciate you stopping by....

Unfortunately, I am not sure how to make more detailed.

Perhaps a picture will help - see below

I am trying to use a slicer that when clicked on an option will filter a table based on that option. The options are funneled through a switch and with this switch I am stuck on how to initiate code or a measure to filter table based on the option chosen. The pic shows the switch code that works when an option is chosen but obviously will only print text....I need it to filter the hosts table as seen in the pciture - thanks!