Forum Discussion

paulpassot's avatar
paulpassot
Frequent Visitor
4 years ago
Solved

Filter context weirdness

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

  • tamerj1's avatar
    tamerj1
    4 years ago

    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.

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

    • paulpassot's avatar
      paulpassot
      Frequent Visitor
      4 years ago

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

       

       

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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 ()
    )
    • paulpassot's avatar
      paulpassot
      Frequent Visitor
      4 years ago

      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$

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

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