Measure not responding to filter

[easyleesie]

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

[easyleesie]

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]

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]

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.

[edhans · Accepted solution]

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

[edhans]

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]

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]

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]

---

@AllisonKennedy wrote:

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. 

---

Hi Allison

Thanks for your reply. Still not working. If I bring in Policy from another table, it shows ALL policies, but with empty fields against all except the two I've filtered on..

It's so frustrating!