Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
jessicaf31
Helper I
Helper I

filter problem in a calculate function - power bi desktop

Hello ! 

 

I don't understand why my measure in Power Bi for Desktop doesn't work : it is a calculate function with a filter function (as I saw here: https://msdn.microsoft.com/fr-fr/library/dn727121.aspx )

 

As you can see below, I would like to see the sum of amount only if the day corresponds to the last day in my database (here the last day is Wednesday, so day 3, and in the first table we can see that the amount for day 3 is 886K and not 6,76M which corresponds to the total of all amounts !) What is wrong in my formula?

 

sumjoursemaine = CALCULATE(SUM(Transactions[amount]);FILTER(ALL(Transactions);Transactions[joursemaine]=[jourdernierjour]))

 

Thanks !!

calculate pb.PNG

1 ACCEPTED SOLUTION

@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.

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

10 REPLIES 10
Greg_Deckler
Super User
Super User

I believe that you are missing a "MAX" in your FILTER. I did this measure:

 

Last Value = CALCULATE(SUM([Value]),FILTER(ALL(dates),[date]=MAX([Date])))

My table had a Date column and Value column, this returned the value from the last date in the table.

 

 

I am not sure how day 3 (Wednesday) is the last date in your table but I assume that's just how your data model works.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Dear Greg,

right now I'm struggling with a FILTER formula in my calculation - COUNTROWS /FILTER combination to be precise, but it doesn't work for me..

I see that your solutions are always workable and I take a dare to ask:

could you advise maybe, what do I do wrong? 🙂

thank you in advance for your help!

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Piechart-diagram-based-on-statistics...
best regards,

Katia

Hi !

Thanks for your answer but I also have this measure, which works.

But the other is different : it is not the max of a date that I want to filter, but only the days which are equals to my last day.

My max(date)=wednesday 24 february, so it is day 3, so I want the sum(value) for all the days 3, and I have this total in my table at the left (886K).

 

This formula shows me the good results : 

sumjoursemaine = CALCULATE(SUM(Transactions[amount]);FILTER(ALL(Transactions);Transactions[joursemaine]=3))

 

But I want the last parameter : 3, to be a variable ("jourdernierjour") :

 

calculate pb2.PNG

When it is a variable it doesn't work :

calculate pb.PNG

 

Thanks again !

 

Jessica

@jessicaf31 - So, I am not super familiar with your data model, but here if you can replicate this, it should get you there.

 

Sum of Last Value = 
VAR LastDayofWeek = AVERAGEX(FILTER(ALL(dates),[date]=MAX([Date])),[DayofWeek])
RETURN (
CALCULATE(SUM([Value]),FILTER(ALL(dates),[DayofWeek]=LastDayofWeek)))

 

So, first, figure out the MAX of the date and what the DayofWeek translates to. Assuming that all Wednesdays are "3", then taking the average will return 3 no matter how many 3's there are. My table has a Date column and a DayofWeek column calculated from the date using WEEKDAY().

 

Use that value to filter the rows in the table and sum the appropriate column ([Value])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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 Smiley Very Happy

 

1) I've created a measure DernierJour = MAX(Transactions[Date])  (DernierJour means LastDay in french Smiley Happy )

-> 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)

 

 

table day.png

 

Whereas when I enter this formula : 

sumjoursemaine = CALCULATE(SUM(Transactions[amount]);FILTER(ALL(Transactions);Transactions[joursemaine]=3))

It works...Smiley Frustrated

 

Thanks again !!

OK, It is very hard to help you when I dont have to much information about your data, BUT, using the values you showed us I found a way to help you with your problem, I can send you the Power BI file if you want just give me your email. Let me know if this helps.

 

Best

Miguel Myers

 

STEP 1.pngSTEP 2.pngSTEP 3.pngSTEP 4.pngSTEP 5.pngSTEP 6.pngSTEP 7.pngSTEP 8.pngSTEP 9.pngSTEP 10.pngSTEP 11.png

@Mykhael Thanks a lot for all these screenshots !! I will try to do the same 🙂

The measure suggested by @Greg_Deckler worked too Smiley Wink

Thanks for your time !! Smiley Happy

 

Jessica

@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.

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thanks a lot it worked !!! Smiley Very Happy

Hello Jessicaf31

 

Can you send me the file? I want to see it and check where the problem comes from? In that way I will help you ASAP on that problem you have.

 

Best

Mykhael

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.