Forum Discussion
Calculate with/without Filter() function and
Hey guys,
So apparently I have been missing something very crucial about how the calculate function works. I am currently on the Datacamp course to get certified, and the question is asking to create a measure that sums a field, filtering it based on the date of another table.
My initial measure was:
But after trying and failing, the "hint" tells me that the correct formula is:
So I must be missing a good definition on how the calculate formula works. Why is it the correct version of the calculate without the Filter() function? It does give very different results, but I am not sure I understand why?
I was reading something that using it without, it moves from row to filter context... and while I thought I had that well understood... I am not seeing that I really don't. Help please?
Hi,
It looks like you are trying to calculate a running total by date. Here's the approach i would use:
- Create a Calendar Table and a relationship (Many to One and Single) from the Recorded Date column to the Date column of the Calendar Table
- To your visual, drag Date from the Calendar Table
- Write this measure
RT = calculate(sum(Expenditures[Budgeted amout]),datesytd(Calendar[Date],"31/12"))
The RT will resent on Jan 1 of each year.
3 Replies
when you use a filter predicate in calculate, power bi transform it into this :
filter (
all ( 'recorded date'[date] ) ,
'recorded date'[date] < = max(expenditures[recorded date])
)so basically there is a difference between this , and the one you are writing, which is :
filter( 'recorded date', 'recorded date' <= max ( expenditures(recorded date] ) )
and the difference is indicative, since in the first, you are removing any filter from the filter context then add the filter , however in the second one, you are adding a filter to the existing filter context.
hope this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠Hi Victor_AguirreV,
I'd like to suggest you take a look at the following blog about row and filter context if it help you to learning with Dax knowledges.
Row Context and Filter Context in DAX - SQLBI
Regards,
Xiaoxin Sheng
Hi,
It looks like you are trying to calculate a running total by date. Here's the approach i would use:
- Create a Calendar Table and a relationship (Many to One and Single) from the Recorded Date column to the Date column of the Calendar Table
- To your visual, drag Date from the Calendar Table
- Write this measure
RT = calculate(sum(Expenditures[Budgeted amout]),datesytd(Calendar[Date],"31/12"))
The RT will resent on Jan 1 of each year.
