Forum Discussion

easyleesie's avatar
easyleesie
Icon for Helper I rankHelper I
6 years ago
Solved

Measure not responding to filter

Hi all

 

New user here. Apologies if this is a stupid question.

 

I have a measure calculating monthly cumulative, but when I put it in a box, it gives the total for the selection against each policy, but I want to see the calculation against each.

 

See here, CumExp (Cumulative Expenses) should be 11,197 for the total. But it's putting that against each.

 

Here's my DAX.

 

 

Thanks

 

Need Help
  • edhans's avatar
    edhans
    6 years ago

    Glad to help easyleesie 

     

    The Star Schema is critical for Power BI to do modeling and analysis right. The rookie mistake is to go grab tables, pull them in, and start creating visuals. And it makes it worse the Power BI Desktop tool encourages this. Another good reference is this article, and SQLBI also has a book on just data modeling called "Analzying Data with Microsoft Power BI and Power Pivot in Excel."

8 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    CALCULATE is redoing the date determination in your measure.

    Try this. There is no need for CALCULATE here, and this should properly get the current date for the <= determination later.

    measure =
VAR CurrentDate =
    MAX( Costs[Date] )
VAR Result =
    SUMX(
        FILTER(
            ALLSELECTED( Costs ),
            CurrentDate
                <= MAX( Cost[Date] )
        ),
        Costs[Expenses]
    )
RETURN
    Result

    ,

     

    • easyleesie's avatar
      easyleesie
      Icon for Helper I rankHelper I
      6 years ago

      Hi edhans

       

      Thanks for your reply. Still not working. Here's yours v my original. Only difference is that now I have a blank field...

      (i've left the filter off, so showing all)

      • AllisonKennedy's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        6 years ago

        I can't be certain without seeing your data model, but I think the problem here might be the ALLSELECTED, which works using the Slicer and report filter context, but not the policy row filter context of the matrix, so if Policy field that you are using in the matrix is not contained in the Costs table, you would get what you expect/want, but if Policy field is in the Costs table, then the ALLSELECTED will give you the total cost for all policies. 

  • easyleesie's avatar
    easyleesie
    Icon for Helper I rankHelper I
    6 years ago

    Hi Guys

     

    Can anyone help here? It seems like such a simple problem, but I'm stuck and it' stopping me from progressing my dashboard. Id be really grateful for any help!

     

    Thanks

     

     

    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      6 years ago

      I'd need to see the PBIX file to really go further. The issue is you don't have a Star Schema and this is making it difficult to write the correct measure, and it is possible it is impossible to write the correct measure depending on how the data is.

      • easyleesie's avatar
        easyleesie
        Icon for Helper I rankHelper I
        6 years ago

        Ok cool. Thanks edhans. That's a useful link. Let me go back to the drawing board. Like I said: I'm new to this...

         

        Thanks for your replies.