Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
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]))
Solved! Go to Solution.
@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 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...
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
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
28 | |
24 | |
24 |