Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
yasbos
Resolver II
Resolver II

Context Filter Help

Hi. I thought I had completely understood how this works, but now I'm doubting myself. You see, I have a calendar table and a service table. They are related 1-to-many on the date. There are certain calculations that require the latest service date, so, I thought I'd just create a measure in the calendar table:

Current_Date= max(service[date]).

 

Here is the problem. The Current_Date is currently the 9th of April. So, When I want to list all of the service reps in a table along with the count of their tickets for the current month, I should get a 0 counts for the reps who have no tickets for the current month, since the Current_Date should be set at April 9, 2023.

In the Service table, I have the following measures:
_Total_Requests=count(Service[Request_ID])

 

ticketsMTD=
      var
vMaxDate=[Current_Date]
      var vMTDRequests=calculate([_Total_Requests],filter(Service,month(Service[Created_Time])=month(vMaxDate) &&        year(Service[Created_Time])=year(vMaxDate)))

 

Now, the problem: I have no tickets for Mark in the current month. The last time Mark had any tickets, it was in October of 2022. His total tickets in October of 2022 were 236. The problem is that, when I create a table visual for the reps and their ticketsMTD, I get 236 for Mark, when I should be getting 0 or blank. Why is this happening? Thanks so much.

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @yasbos ,

It seems that you are trying to get the maximum value of the [Date] column in the service table and ignore the filtering context on this column.

Please try this.

Current_Date =
CALCULATE ( MAX ( service[date] ), ALL () )

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Share some data to work with and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-cgao-msft
Community Support
Community Support

Hi @yasbos ,

It seems that you are trying to get the maximum value of the [Date] column in the service table and ignore the filtering context on this column.

Please try this.

Current_Date =
CALCULATE ( MAX ( service[date] ), ALL () )

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Thanks, @v-cgao-msft 

amitchandak
Super User
Super User

@yasbos , You should use today for current month

 

ticketsMTD=
      var
vMaxDate=today()
      var vMTDRequests=calculate([_Total_Requests],filter(Service,month(Service[Created_Time])=month(vMaxDate) &&        year(Service[Created_Time])=year(vMaxDate)))
 
 
also for last available month
 
ticketsMTD=
      var
vMaxDate=maxX(allselected(service), service[date])
      var vMTDRequests=calculate([_Total_Requests],filter(Service,month(Service[Created_Time])=month(vMaxDate) &&        year(Service[Created_Time])=year(vMaxDate)))
Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Thanks. It's just that I need the current month to really be the last month in the data--not the current calendar month--hence my approach. I guess I'll have to change and make a compromise, to get it to work.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.