Slicer with only Limited Data

Data Visualizations
1

Hi,
Learner over here. I wanted to create a slicer with only limited data for filtration.

My current data model looks like this as per below:
image

What I’m trying to achieve is create a slicer for BinCode that includes only the values where Location code = CONSIGN

One way I’ve found to solve this issue is by creating a visual level filter like this

image

I was wondering is there any other efficient way to do this by measures or Virtual tables?

Thanks in advance.

2

@40ART ,

Neither measures nor virtual tables will help you here. Power BI does not allow you to slice on a measure, and virtual tables exist only within the context of measures.

The other way to do this outside the filter pane is to use a calculated table such as

Consign Table =
FILTER (
    binContents,
    locationCode = "CONSIGN"
)

You could then connect that table to your data model and drop the binCode field into your slicer, without having to use the filter pane, but I’m not sure that that represents any significant advantage.

I hope this is helpful.

  • Brian

PS – for a deep dive into the differences between physical tables, calculated/DAX expression tables and virtual tables, you may want to check out the following video:

1 Like
3

Thank you Brian for the detailed explanation :slight_smile:

But in terms of performance, which method would be more suitable? Plus in the video also it mentions generally to stay away from calculated tables/ columns. So I’m leaning towards what I’ve done previously.

Is that a better option in terms of performance?

Thanks again :slight_smile: :slight_smile:

4

@40ART ,

I think the filter pane approach you’ve taken is the way to go. Simpler, doesn’t complicate your data model, doesn’t add an extra table to the load time, etc.

I haven’t tested the two approaches against each other in Performance Analyzer, but I suspect the differences would be negligible. Would be easy to test though - open a blank page with two slicers.
Just have a visual-level filter on one slicer, and build the other from the relevant column of the calculated table table and measure the elasped timing as both refresh.

  • Brian
5

Legend. Thanks.

I will definitely take this approach whenever possible.

6

@40ART ,

Glad to help.

FYI - for some reason, the filter pane gets mad disrespect from a lot of Power BI users, who tip toward DAX-based solutions instead. Here’s a comparison I did last year where one line of DAX and the filter pane accomplished the same thing that it took DAX alone 59 lines to do. I’ve always taken the approach that the best and fastest DAX is the DAX you don’t have to write (even though I love writing DAX…)

  • Brian
2 Likes