CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
KieranIngram
KieranIngram

Reputation: 91

Power BI - how can I exclude certain rows dynamically? I always want rows of one type but want EITHER rows with col1=A or B, never both

Not sure how best to describe this but here goes.
I have a datatable with rows of resource allocation by staff member, project and date.
I want to always include rows relating to most projects, but there are rows for two specific projects - call them A and B - and I only ever want to include one of these.
I'd like a slicer or some kind of button to specify whether I use A or B, but in my visualisations etc I'd like to display everything except the one not chosen. Dummy data

Any ideas?

Upvotes: 0

Views: 3743

Answers (1)

Joao Leal
Joao Leal

Reputation: 5542

Not sure I understand your requirement correctly, but this solution should apply.

Suggestion 1

Create a custom column:

Col1 = SWITCH(table[project], 
   "A", "A project",
   "B", "B project",
   "Other projects")

Create a slicer based on Col1 and then you can use that as a slicer (it will show 3 values).

Suggestion 2

One other alternative could be to create a separate table with a many to many relationship to your data table, for example, for this table:

data

You can create the following table:

types

Notice how I've repeated project names (other than Assumption and Actuals and have assigned them the two different types). Then you just need a many-to-many relationship between project and project name and use a slicer based on "Type".

Suggestion 3

Create a table with just the 2 options (no relationship to the rest of the model):

enter image description here

Then in your measures, instead of just doing a plain SUM, take the Option as a filter, for example:

Days Allocated = 
VAR option = SELECTEDVALUE(Options[Project], "AL (Actual)") //default to Actual

RETURN CALCULATE(SUM(Data[Allocation]), KEEPFILTERS(Data[Project] <> option))

demo

Upvotes: 1

Related Questions