Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filter context not passing onto measure

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?? 

 

DAX
Filter
metric
relationship
  • johnt75's avatar
    johnt75
    3 years ago

    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
    )

8 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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 

       

       

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        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
    )