cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Hi @Matt_R 

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

5 REPLIES 5
Highlighted
Super User IV
Super User IV

@Matt_R - 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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Hi @Matt_R 

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Highlighted
Super User IV
Super User IV

@Matt_R , 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

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

 

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


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

@amitchandak @az38 @Greg_Deckler 

 

Thanks for your replies, I will try what you suggested and report back.

Highlighted

@az38 your solution was exactly what I was looking for. Thank you very much.

 

I will try the other things suggested as well. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors