Forum Discussion
Helper IMeasure not responding to filter
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."
Community ChampionCALCULATE 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
,
easyleesieHi 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!
