Forum Discussion
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 t...
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 __ResultYet another reason to not use CALCULATE...
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
__ResultYet another reason to not use CALCULATE...
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!