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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
nleuck
Post Patron
Post Patron

Ignoring page level filters in measure

Hello All,

 

I have a report page with a page of last 30 days. The bars are as is: Tickets Created (blue), Tickets Resolved (orange), and Tickets Open (purple). I originally had a running total for the last 30 days, but that is not what is exactly wanted. I need to get the total tickets open at the end of day for the entire dataset. As you can see my purple bar is the same across all the days. For example if the total open tickets as of June 7 was 50 and at the end of the day June 8 there were 5 more open tickets then the total for June 8 would be 55.

 

Here is the formula I'm using:

 

Total Open Tickets =

  CALCULATE(DISTINCTCOUNT(DataTable[TicketId]),
         ISBLANK(Data[ResolvedDate]),

         ALL(CalendarTable[Date]))

 

open tickets last 30 days.png

 

Any help would be greatly appreciated.

 

Thanks!

1 ACCEPTED SOLUTION

Hi @nleuck ,

 

I forgot to add the ALLSELECTED() function.

Try this again.

And maybe my .pbix file can give you some help.


Total Open Tickets = 
CALCULATE(
    DISTINCTCOUNT( DataTable[TicketId] ),
    //Filter out the resolved tickets
    FILTER(
        Data,
        Data[ResolvedDate] = BLANK()
    ),
    //Cumulative calculation
    FILTER(
        ALLSELECTED(CalendarTable),
        CalendarTable[Date] <= MAX( CalendarTable[Date] )
    )
)

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

4 REPLIES 4
v-lionel-msft
Community Support
Community Support

Hi @nleuck ,

 

Has your problem been solved?

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lionel-msft
Community Support
Community Support

Hi @nleuck ,

 

Please refer to the formula, you shouldn't use  ALL(CalendarTable[Date]) function. According to my understanding, you need to accumulate tickets according to the calendar date.

Total Open Tickets = 
CALCULATE(
    DISTINCTCOUNT( DataTable[TicketId] ),
    //Filter out the resolved tickets
    FILTER(
        Data,
        Data[ResolvedDate] <> BLANK()
    ),
    //Cumulative calculation
    FILTER(
        CalendarTable,
        CalendarTable[Date] <= MAX( CalendarTable[Date] )
    )
)

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

@v-lionel-msft 

 

First, I had to make a change to your formula. Data[ResolvedDate] <> Blank() needs to be Data[Resolved Date] = Blank(), because I want to find the total open tickets.

 

Even when I do that this formula gives me the same results as mine. Currently it's showing that we have a total of 52 open tickets, across everyday of the last 30 days. On June 8th the total was 49 and on June 9th there were 3 more open tickets so now the total is 52.

Hi @nleuck ,

 

I forgot to add the ALLSELECTED() function.

Try this again.

And maybe my .pbix file can give you some help.


Total Open Tickets = 
CALCULATE(
    DISTINCTCOUNT( DataTable[TicketId] ),
    //Filter out the resolved tickets
    FILTER(
        Data,
        Data[ResolvedDate] = BLANK()
    ),
    //Cumulative calculation
    FILTER(
        ALLSELECTED(CalendarTable),
        CalendarTable[Date] <= MAX( CalendarTable[Date] )
    )
)

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

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! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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