CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
luamar
luamar

Reputation: 314

Power BI DAX: date slicer filter does not affect custom measure that uses CALCULATE

I am struggling to understand the behavior of the filter slicers and custom measures when using the CALCULATE DAX formula. My goal is pretty simple: I have a sales table that includes both the sales date and the ship date (see the below screenshot), and I have created two custom measures:

Sold products = CALCULATE(
    COUNT('dummy data'[SaleID])
)

Shipped products = CALCULATE(
    COUNT('dummy data'[SaleID]),
    NOT(ISBLANK('dummy data'[Ship date]))
)

Note that the table will always have a sale date but may not have a shipping date (yet), also shipping date will always be a "past date".

I have added a filter slicer in the canvas using the 'dummy data'[Ship date] column (note that is the same field I am using on my Shipped products measure).

When I attempt to use the date filter slicer (e.g., shipped products between two specific dates), the Shipped products measure does not change based on the filters applied.

Why does this happen? I would appreciate any extensive explanation that may help me understand the behavior of this scenario and, ideally, a solution of how I can make this work, how I can use the filter slicer for the Ship Date at the same time it is being used as part of the CALCULATE filter condition.

Table:

enter image description here

Canvas report without filter slicer

enter image description here

Canvas report with specific date range applied on filter slicer:

enter image description here

Expected result: Shipped products should be 7

Thanks

Upvotes: 1

Views: 4151

Answers (1)

davidebacci
davidebacci

Reputation: 30254

You're overwriting the filter context in your measure.

CALCULATE() is used to overwrite the filter context and the part that says:

NOT(ISBLANK('dummy data'[Ship date]))

is overwriting that context. Also, for it to respond to slicers, you'll need to use ALLSELECTED() at some point.

You should really be using a date table too.

In your specific case, a simple measure like this will work and respond to slicers.

Shipped products = COUNT('dummy data'[SaleID])

I suggest you read more about CALCULATE() here:

https://www.sqlbi.com/articles/introducing-calculate-in-dax/

Upvotes: 0

Related Questions