Forum Discussion

Davidian's avatar
Davidian
Regular Visitor
2 years ago
Solved

Measure not filtering correctly (But sometimes does)

I have 2 dropdowns

1 is filled with dates, from a dates table that filters the tables for the tables - however, some of the tables are not filtered by date so is not joined.

There is a measure on this table:

SelectedDate = SELECTEDVALUE(DistinctDatesClient[Date])
 
Another is populated with names of clients, linked to our client table. This table is filtered to all the reporting tables by the client's portfolio Id. 
There is a measure on this table:
SelectedPortfolioFulcrumEntityId = SELECTEDVALUE(ClientDetails[Portfolio_FulcrumEntityId])

On the summary page we have a selection of values that are from all the reporting tables, including those that are unfiltered by date, based on the closest date (on or before) the selected date. 
 
I created a measure to bring back the date:
 
FLDate Before Date = CALCULATE(MAX(FundingLevel[Date]),FILTER(FundingLevel,FundingLevel[Date]<=[SelectedDate]&&FundingLevel[Portfolio_FulcrumEntityId]=[SelectedPortfolioFulcrumEntityId]))
 
And this worked. 
 
I used this in a measure:
ALSRecalDate = CALCULATE(MAX(FundingLevel[ALSReclaibrationDate]),FILTER(FundingLevel,FundingLevel[Date]=[FLDate Before Date]&&FundingLevel[Portfolio_FulcrumEntityId]=[SelectedPortfolioFulcrumEntityId]))
 
And it worked.
 
I then used it in some value measures, e.g. The following are all on the FundingLevel table.

LiabilitySumWithFilter = CALCULATE(SUM(FundingLevel[LiabilityValue]), FILTER(FundingLevel,FundingLevel[Date]=[FLDate Before Date]&&FundingLevel[Portfolio_FulcrumEntityId]=[SelectedPortfolioFulcrumEntityId]))


But this did not work - it summed all values together. 
It had to use the calculation of the date measure explicitly like:

 

LiabilitySumWithFilter = CALCULATE(SUM(FundingLevel[LiabilityValue]), FILTER(FundingLevel,FundingLevel[Date]=CALCULATE(MAX(FundingLevel[Date]),FILTER(FundingLevel,FundingLevel[Date]<=[SelectedDate]&&FundingLevel[Portfolio_FulcrumEntityId]=[SelectedPortfolioFulcrumEntityId]))&&FundingLevel[Portfolio_FulcrumEntityId]=[SelectedPortfolioFulcrumEntityId]))


There's clearly something I am missing - I don't doubt it is my lack of knowledge, but it does seem odd that it works in some circumstances and not another.
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Davidian Using measures in the filter clause of a CALCULATE can do wacky things. I generally use VAR statements to do this instead so something like.

    LiabilitySumWithFilter = 
  VAR __FLDateBeforeDate = [FLDate Before Date]
  VAR __SelectedPortfolioFulcrumEntityId = [SelectedPortfolioFulcrumEntityId]
  VAR __Result = 
CALCULATE(SUM(FundingLevel[LiabilityValue]), FILTER(FundingLevel,FundingLevel[Date]=__FLDateBeforeDate &&FundingLevel[Portfolio_FulcrumEntityId]=__SelectedPortfolioFulcrumEntityId ))
RETURN
  __Result

    Yet another reason to not use CALCULATE...

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Davidian Using measures in the filter clause of a CALCULATE can do wacky things. I generally use VAR statements to do this instead so something like.

    LiabilitySumWithFilter = 
  VAR __FLDateBeforeDate = [FLDate Before Date]
  VAR __SelectedPortfolioFulcrumEntityId = [SelectedPortfolioFulcrumEntityId]
  VAR __Result = 
CALCULATE(SUM(FundingLevel[LiabilityValue]), FILTER(FundingLevel,FundingLevel[Date]=__FLDateBeforeDate &&FundingLevel[Portfolio_FulcrumEntityId]=__SelectedPortfolioFulcrumEntityId ))
RETURN
  __Result

    Yet another reason to not use CALCULATE...

    • Davidian's avatar
      Davidian
      Regular Visitor
      2 years ago

      Awesome, thank you Greg_Deckler - I will try this on monday (caught the email as I was just wrapping up for the weekend) 

      Sounds like a sensible option to go with the measures. 
      It might alctually solve some other issues I had that I ended up doing different way in the back end (as using SQL ended up being much easier)

       

      Thank you, and have a good weekend!

    • Davidian's avatar
      Davidian
      Regular Visitor
      2 years ago

      Good morning, hope you had a fantastic weekend. 

      Just wanted to say thanks again, I have started going through my formula replacing them with the methodology you mention and it seems to be working (the figures are correct like my "long winded" versions) so thank you so much for that. 

      Seems really strange measures operate that way - is this an "error" with Dax that one just has to accept and will likely never be fixed, or is it actually considered a "feature"? lol