topic Re: Filter context not passing onto measure in DAX Commands and Tips

Filter context not passing onto measure

Anonymous — Wed, 15 Feb 2023 09:14:49 GMT

Hi Power BI Brains!

I need a bit of help please, I've been stuck on this problem and don't fully understand why it's happening. I've got a dataset that contains a Calendar table and a Sales Detail fact table that have a one to many join between them, single direction from Calendar to the Sales table.

I've created a table of explicit measures: a set of measures for the current year and a set for the previous year (based on the dates in my Calendar table where each calendar date has a YOY date in the financial year which does not align with the standard gregorian calendar).

Sales Unit Va lue= sum('Sales Daily Detail'[Invoiced Sales Unit Value])

Sales Unit Value LY =

var _MaxDate = CALCULATE(MAX('Sales Daily Detail'[YOY Event Date]))

var _MinDate = CALCULATE(MIN('Sales Daily Detail'[YOY Event Date]))

RETURN CALCULATE([Sales Unit Value],ALL('Sales Daily Detail'),'Sales Daily Detail'[Event Date]>=_MinDate&&'Sales Daily Detail'[Event Date]<=_MaxDate)

The LY measure works as intended, it's supposed to be flexible so that when the user uses the dataset for self-serve queries they can use the LY metric for whatever date range they need. If I filter the Calendar table with a slicer, I get the correct LY value, happy with that part.

But... this is where the problem comes, I then I created a DateSelections table as per this video (https://www.youtube.com/watch?v=fKygF7VEJnQ) to provide my users with a pre-defined list of time periods they can select in the report as well as a Custom Date range, because this dataset is also used for a lot of dashboards.

The DateSelections table that I created for my pre-defined periods has a one to many relationship filtering both ways to the Calendar table and has *no* relationship to the fact table.

If I select let's say "Week to Date" on my dropdown using DateSelections then it goes and filters my Calendar table which then correctly filters my Sales Fact table and returns the correct Sales Unit Value. Here's the weird part... Sales Unit Value LY goes blank! If I create a slicer that has any field in from the Calendar table and I use that to filter the sales fact talbe, Sales Unit Value LY is correctly returned.

If DateSelections filters Calendar and Calendar filters Sales fact then why is the Sales Unit Value LY metric not populating at all when I make a selection in DateSelections?

I am quite confused, I suspect it's to do with not being able to pass a filter context onto a second table or something? I tried adding an inactive relationship between DateSelections and the Sales Fact and then use USERELATIONSHIP but the problem persisted.

Any thoughts on what I'm doing wrong??

Re: Filter context not passing onto measure

johnt75 — Wed, 15 Feb 2023 09:45:34 GMT

In your measure you are placing a filter on the date column in the fact table, you need to put the filter on the date table. The reason that the measure is returning blank is that there is a filter on the date table for dates this year, which passes to the fact table, and then a filter on the fact table for dates last year. those 2 filters combined will result in an empty table, so the measure returns blank.

By putting the filter on the date table, when you add the filter for last year DAX will automatically replace any existing filters rather than combining them, as long as your date table is properly marked as a date table.

Re: Filter context not passing onto measure

Anonymous — Wed, 15 Feb 2023 10:31:34 GMT

Hi johnt75,

So if I understand your suggestion then the LY value should have the following DAX:

Sales Unit Value LY =

var _MaxDate = CALCULATE(MAX('Calendar by Calendar Date'[YOY Financial Calendar Date]))

var _MinDate = CALCULATE(MIN('Calendar by Calendar Date'[YOY Financial Calendar Date]))

RETURN CALCULATE([Sales Unit Value],ALL('Sales Daily Detail'),'Sales Daily Detail'[Event Date]>=_MinDate&&'Sales Daily Detail'[Event Date]<=_MaxDate)

Just tried this and it's working for picking up all the dates for LY that exist even beyond today's date, which is something my previous formula couldn't do so thank you for that, that's one problem fixed!

However when I select one of the pre-defined options in my DateSelections table the LY still goes blank. There is no direct relationship between this manually created table and my sales fact table, but I would have expected the relationship between DateSelections and my Calendar to then filter the Sales table as it does when I select a table in the Calendar table directly.

Included the table relationships for clarity

Re: Filter context not passing onto measure

johnt75 — Wed, 15 Feb 2023 10:57:43 GMT

You've still got the filter on the fact table. Try

Sales Unit Value LY = VAR _MaxDate = CALCULATE ( MAX ( 'Calendar by Calendar Date'[YOY Financial Calendar Date] ) ) VAR _MinDate = CALCULATE ( MIN ( 'Calendar by Calendar Date'[YOY Financial Calendar Date] ) ) RETURN CALCULATE ( [Sales Unit Value], ALL ( 'Sales Daily Detail' ), 'Calendar by Calendar Date'[Date] >= _MinDate && 'Calendar by Calendar Date'[Date] <= _MaxDate )

Re: Filter context not passing onto measure

Anonymous — Wed, 15 Feb 2023 12:55:54 GMT

I've popped in the formula you were suggesting, wihtout selecting anything from the slicer that shows the Type field from DateSelections table I get values, same as before so that's working however....

Then I select WTD for instance and the LY value disappears still. I know that my fact table contains that data for last year for this week, if you manually query the data for those dates, the values are there. And the formula does return the correct LY value if I use any slicers that point to my Calendar table and not the DateSelection table.

Re: Filter context not passing onto measure

johnt75 — Wed, 15 Feb 2023 13:39:18 GMT

Change the measure to store the result of the final CALCULATE in a variable and then you can check that the dates being returned are what you expect by having the measure return first the min date and then change it again to return the max date.

Re: Filter context not passing onto measure

Anonymous — Wed, 15 Feb 2023 13:53:14 GMT

I tested this early on in my testing process and the dates returned are correct.

Re: Filter context not passing onto measure

johnt75 — Wed, 15 Feb 2023 15:29:40 GMT

You need to explicitly remove the filters from the date selection table, the automatic removal of filters only applies to the date table itself, not the expanded table

Sales Unit Value LY = VAR _MaxDate = CALCULATE ( MAX ( 'Calendar by Calendar Date'[YOY Financial Calendar Date] ) ) VAR _MinDate = CALCULATE ( MIN ( 'Calendar by Calendar Date'[YOY Financial Calendar Date] ) ) RETURN CALCULATE ( [Sales Unit Value], ALL ( 'Sales Daily Detail' ), REMOVEFILTERS ( 'Date Selections' ), 'Calendar by Calendar Date'[Date] >= _MinDate && 'Calendar by Calendar Date'[Date] <= _MaxDate )

Re: Filter context not passing onto measure

Anonymous — Wed, 15 Feb 2023 15:51:03 GMT

Thank you, using the REMOVEFILTERS actually resolved it!