topic Re: Filter context weirdness in DAX Commands and Tips

Filter context weirdness

paulpassot — Fri, 24 Jun 2022 00:05:40 GMT

Hi there!

Perhaps a newbie question, but I can't wrap my head around how a measure reacts to filters...

I have a measure that calculates sum of sales for the most recent day in the data :

Max Day Sales =
CALCULATE(

SUM( Invoices_LQ[InvoiceLine.Amount] ),

Invoices_LQ[PostDate_DateFormat]=MAXX(

ALL( Invoices_LQ[PostDate_DateFormat] ),
Invoices_LQ[PostDate_DateFormat] )

)

When I create a table with Invoices_LQ[PostDate_DateFormat] and this measure, I can see indeed the sales for the last date for every Invoices_LQ[PostDate_DateFormat] line :

So far, so good.

However, if I create another visual showing Invoices_LQ[PostDate_DateFormat] and I select a value in there, sometimes it changes my Max Day Sales measure :

On top of that, it only seems to do so for certain Invoices_LQ[PostDate_DateFormat] only, and I haven't been able to identify some sort of pattern.

I'm guessing it has to do with some sort of context filtering I'm not understanding.

Still fairly new to PowerBI but this has given me a pretty bad headache!!

Anyone has a clue?

Thanks a lot!!

Re: Filter context weirdness

amitchandak — Fri, 24 Jun 2022 02:14:03 GMT

paulpassot , Try like

Max Day Sales =
CALCULATE(
SUM( Invoices_LQ[InvoiceLine.Amount] ),
lastdate(Invoices_LQ[PostDate_DateFormat] )
)

for date

MAxx(ALL( Invoices_LQ[PostDate_DateFormat] ), Invoices_LQ[PostDate_DateFormat] )

Re: Filter context weirdness

paulpassot — Fri, 24 Jun 2022 02:41:59 GMT

Unfortunately it doesn't work, it gives me the values for each date:

Re: Filter context weirdness

tamerj1 — Fri, 24 Jun 2022 02:49:23 GMT

Hi paulpassot

please try

Max Day Sales = VAR MaxDate = CALCULATE ( MAX ( Invoices_LQ[PostDate_DateFormat] ), REMOVEFILTERS () ) RETURN CALCULATE ( SUM ( Invoices_LQ[InvoiceLine.Amount] ), Invoices_LQ[PostDate_DateFormat] = MaxDate, REMOVEFILTERS () )

Re: Filter context weirdness

paulpassot — Fri, 24 Jun 2022 03:09:34 GMT

Hi, thanks for the quick reply!

Interestingly, it gives the same result across dates (which is good), but it doesn't seem to give the right number:

I would expect 4,744$, not 5,358$.

I understand the logic of your code though, and I can't figure out how it comes up to 5,358$

Re: Filter context weirdness

tamerj1 — Fri, 24 Jun 2022 03:16:39 GMT

paulpassot
I don't know how your data looks like but seems to me there are other filters. You may try **bleep** this way

Max Day Sales = VAR MaxDate = CALCULATE ( MAX ( Invoices_LQ[PostDate_DateFormat] ), ALL ( Invoices_LQ[PostDate_DateFormat] ) ) RETURN CALCULATE ( SUM ( Invoices_LQ[InvoiceLine.Amount] ), Invoices_LQ[PostDate_DateFormat] = MaxDate, ALL ( Invoices_LQ[PostDate_DateFormat] ) )

Re: Filter context weirdness

paulpassot — Fri, 24 Jun 2022 03:23:34 GMT

So strange... it gives me the same result as my initial formula. Randomly giving a different number depending on what I select in another visual:

Here it is ok, filtered on 21st of june

Here it gives me a different value (wrong) when filtered on the 20th of june:

My datamodel is incredibly simple: single table with sales transactions in rows, a column for price and a column for date. I made a calculated column that "rounds" the post date (which is datetime) to a regular date :

PostDate_DateFormat = DATE(YEAR(Invoices_LQ[InvoiceHeader.Column1.invoicePostDate]),MONTH(Invoices_LQ[InvoiceHeader.Column1.invoicePostDate]),DAY(Invoices_LQ[InvoiceHeader.Column1.invoicePostDate]))

That's the column I leverage in my Max Day Sales formula. Do you think it could come from there?

Re: Filter context weirdness

tamerj1 — Fri, 24 Jun 2022 03:42:47 GMT

paulpassot
Aparently this is Auto-Exist problem.

First step try to manually calculate the last day sales. If it does not match the result of my first solution then you need to have a date table that filters your data set. This will totally eleiminate the problem.