CODEWITHSUNDEEP
powerbidax
Dru
Dru

Reputation: 73

DAX, filter context when using the ALL or Filter functions, if other filters result in null values

I have two slicers that affect my data, a date between slicer and a numeric value >= to slicer based on a unit price.

I am looking at the sum(Bookings) with the applied filters, order date, and unit price.

I also have a measure looking at the sum(Bookings) based on the date selection only, this measure should ignore any values in the numeric slicer.

I have tried the following, but all three result in null values when there is data present:

CALCULATE(SUM(Bookings[BOOKINGS]),ALL(Bookings[UNIT_PRICE]))

CALCULATE(SUM(Bookings[BOOKINGS]),FILTER(ALLEXCEPT(Bookings,Bookings[ORDER_DATE]),SUM(Bookings[BOOKINGS])<>0))

CALCULATE(SUM(Bookings[BOOKINGS]),ALLEXCEPT(Bookings,Bookings[ORDER_DATE]))

These all work as expected, except when the combination of Date and Price filters results in a null value. When this occurs the measure also returns a null value even if there are values for that date.

Example below.

Example

Upvotes: 0

Views: 494

Answers (1)

user8078111
user8078111

Reputation: 468

If you don't want your visual to be affected by a particular filter, you can change the behaviour of the filter for the visual by changing the interaction for the filter.

Just click on the slicer and go to Format>> Edit Interactions >> Click on the None icon of the visual that you want to disable the filter for.

Editing the interaction

Upvotes: 1

Related Questions