Forum Discussion
How to workaround Calculate function ignoring filter context
Hello,
I have 2 measures. The first one simply counts all orders and the 2nd one does the same thing but for a specific date.
This works fine however if I want to group the second measure by sales agent I get the same value for each agent, which is of course wrong. I need the number filtered by sales agent. I've googled and searched these forums but I cant find anything specific to working around this issue. My measure uses calculate so I'm hoping it is a simple substituion for another function but I can't find one that works. Here are my measures.
Measure 1:
AllOrders =
COUNTROWS('Orders')
Measure 2:
OrdersPrevDay =
VAR PDate = LASTDATE('Calendar'[Date])
RETURN
CALCULATE ( [AllOrders],
FILTER ( ALL ( 'Calendar' ),
'Calendar'[Date] > PDate - 1 &&
'Calendar'[Date] <= PDate )) + 0
What I'm looking for is
OrdersPrevDay SalesAgent
6 Name 1
5 Name 2
8 Name 3
7 Name 4
8 Name 5
Does anyone have any suggestions? thanks.
Hi Anonymous
If I understand you correct try to use ALLEXCEPT() like
OrdersPrevDay = VAR PDate = LASTDATE('Calendar'[Date]) RETURN CALCULATE ( [AllOrders], ALLEXCEPT('Orders', 'Orders'[SalesAgent]), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] > PDate - 1 && 'Calendar'[Date] <= PDate )) + 0
5 Replies
Anonymous - That sounds like a missing relationship. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi Anonymous
If I understand you correct try to use ALLEXCEPT() like
OrdersPrevDay = VAR PDate = LASTDATE('Calendar'[Date]) RETURN CALCULATE ( [AllOrders], ALLEXCEPT('Orders', 'Orders'[SalesAgent]), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] > PDate - 1 && 'Calendar'[Date] <= PDate )) + 0az38 your solution was exactly what I was looking for. Thank you very much.
I will try the other things suggested as well.
Anonymous , make sure you have date table joined with you date and try
Last Day = CALCULATE([AllOrders]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE([AllOrders]), previousday('Date'[Date]))refer
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Thanks for your replies, I will try what you suggested and report back.
