Is there a way to only apply a filter on a specific subset of your data?

[Anonymous]

I have a data table that includes present values for different operations within the company. Within the list I have current operations and several potetnial future projects. I have a stacked column chart which has business value from operations split over geographical region. What I would like to do is introduce a slicer with a list of the potential projects such that when they are selected, they are added to the data in the stacked column chart (in order to take a view of what the business would look like if the projects were completed). At the moment, when I have a slicer with list of projects, and I select one or two of them, it removes operational data from the chart. One way round it is to have "Operational" listed in the slicer and always selected, but I would rather not have the option to deslect it. Data looks like the following:

Region   Project           Value

US          Operational       100

UK          Operational       500

JP          Operational        20

UK          Project A           1

UK           Project B           3

UK          Project C           2 

US          Project D           5

So I would like the slicer to have Project A, Project B, Project C, Project D. When none are selected, only operational will show in the stacked chart. When Project A is selected, Operational and Project A are shown in the stacked chart.

I'm relatively new to Power BI so I might have missed something fundamental, but I cannot, yet, figure it out. Any help is greatly appreciated

[v-lili6-msft · Accepted solution]

hi @Anonymous 

You could try this way as below:

Step1:

you need a dim project table that does not contain "Operational", then do not create the relationship with fact table.

Step2:

Create a measure as below:

Measure = 
IF(ISFILTERED('Dim Project'[Project]),
CALCULATE(SUM('Table'[Value]),FILTER('Table', 'Table'[Project] in VALUES('Dim Project'[Project]) ||'Table'[Project]="Operational")),
CALCULATE(SUM('Table'[Value]),FILTER('Table', 'Table'[Project]="Operational"))
  )

Step3:

Now use project from dim table as a slicer and use this measure instead of value field in the visual

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

Fantastic! That's exactly what I needed, thanks very much

[v-lili6-msft]

hi @Anonymous 

You are welcome, could you please accept my reply as answer, that way, other community members would easily find the solution when they get same issues.  😁

Regards,

Lin

[Greg_Deckler]

Probably disconnect your slicer from your other visualization so that it does not filter. Then you could write your measure such that it includes all operational stuff and whatever projects are selected. You could also use ALL and essentially do the same thing if you do not disconnect the slicer from the visual.