Forum Discussion
filter problem in a calculate function - power bi desktop
jessicaf31 - I believe your problem is context with your jourdernierjour and/or DernierJour measure. In the context of your report, it shows 3, but in the row context when your sumjoursemaine measure is calculating, DernierJour always equals the MAX of that row and thus jourdernierjour always equals the joursemaine column.
Try changing your DernierJour measure to this:
DernierJour = MAXX(ALL(Transactions),Transactions[Date])
What this does is force DernierJour to always evaluate in the context of all of the rows, which means that DerierJour will always be the MAX date within the table, which means that jourdernierjour will always be the value of the weekday of the MAX date in the table, which *should* mean that you get the right answer in your report.
Greg_Deckler Thanks again for taking time to answer and help me.
I have done the same but it is still not working.
I will describe all the steps I have done and maybe you will detect my mistake :smileyvery-happy:
1) I've created a measure DernierJour = MAX(Transactions[Date]) (DernierJour means LastDay in french :smileyhappy: )
-> it returns Wednesday 24 February 2016
2) I've created a measure jourdernierjour = WEEKDAY([DernierJour];2) (for knowing the day of the LastDay)
-> it returns 3
3) I've created a column joursemaine = WEEKDAY(Transactions[Date];2) (for knowing each weekday for each date)
3) Now I want to know the total of amount only for the days which are wednesdays (LastDay) so I've created the measure :
sumjoursemaine = CALCULATE(SUM(Transactions[amount]);FILTER(ALL(Transactions);Transactions[joursemaine]=[jourdernierjour]))
-> it returns the total of all Transactions[amount] (6,8M) without filtering on the last day (I want 886K)
Whereas when I enter this formula :
sumjoursemaine = CALCULATE(SUM(Transactions[amount]);FILTER(ALL(Transactions);Transactions[joursemaine]=3))
It works...:smileyfrustrated:
Thanks again !!
jessicaf31 - I believe your problem is context with your jourdernierjour and/or DernierJour measure. In the context of your report, it shows 3, but in the row context when your sumjoursemaine measure is calculating, DernierJour always equals the MAX of that row and thus jourdernierjour always equals the joursemaine column.
Try changing your DernierJour measure to this:
DernierJour = MAXX(ALL(Transactions),Transactions[Date])
What this does is force DernierJour to always evaluate in the context of all of the rows, which means that DerierJour will always be the MAX date within the table, which means that jourdernierjour will always be the value of the weekday of the MAX date in the table, which *should* mean that you get the right answer in your report.
Greg_Deckler Thanks a lot it worked !!! :smileyvery-happy: