PowerBI DAX measure - Filter rows where column has to have a specific value

Question

I have a measure like below

CALCULATE(SUM(Sales[Value]); FILTER(ALL(Sales); Sales[Month] IN {"01"; "02"}))

When filtering on a slicer e.g. Department The Sales value is not changing, it's always stuck at 3500. Is there a way to filter through the rows even if the Sales[Month] Column can only be 01 02

Below is a one-to-many relationship of two tables.

Employees

Employee(Unique) Department
Cosmo               A1
Kramer              A1
Kessler             B1

Sales

Employee   Month    Value
Kramer      01      2500
Kramer      03      5000
Kessler     01      500
Cosmo       02      500

Using the measure gives me row 1,3 and 4 and returns a value of 3500. If I pick department B1 I would like it to return row 3 (500) and A1 row 1 and 4 (3000).

Is there some way to make this happen? I can not use the "Edit interactions" in format, since this measure is to be used in a visual that uses other measures. I would like the measure to be responsible alone for this interaction.

Edit: If I only check month 03 in my slicer, the measure should still show values for month 01 and 02 and keep adjusting to department slicer.

Edit2: I have some pictures of my problem below:

Relationships

image of below shows Value and 1&2_Target are both 500. Whereas only Value should show 500 and 1&2_Target should be 3000. I'd like the graph to simply ignore the date slicer by the dax formula.

Alexis Olson Alexis Olson · Accepted Answer · 2018-01-18T20:54:36

I believe what is happening is that your ALL(Sales) part is stripping off the department filter context from the slicer.

There are a variety of ways to fix this. You could probably use ALLSELECTED or ALLEXCEPT inside your FILTER() to get what you want, but you can also simply do this:

= CALCULATE(SUM(Sales[Value]); Sales[Month] IN {"01"; "02"})

In this case, you are just replacing the [Month] filter context with what you want it to be and not touching the filter context for [Department].

Edit: If the above doesn't work, try resetting the Sales[Month] filter context and then applying the specific filter as follows:

= CALCULATE(SUM(Sales[Value]); ALL(Sales[Month]); Sales[Month] IN {"01"; "02"})

If you have months in your slicer, those get passed to the filter context. The ALL() function clears the filter context for [Month] and then you apply the particular ones you want.

Edit 2: I see that you have a Date table. Please try replacing

1&2_Target = CALCULATE(SUM(Sales[Value]), ALL(Sales[Date]), Sales[Date] IN {"1", "2"})

with this

1&2_Target = CALCULATE(SUM(Sales[Value]), ALL('Date'[Date]), Sales[Date] IN {"1", "2"})

PowerBI DAX measure - Filter rows where column has to have a specific value

1 Answers