Ignoring page level filters in measure

[nleuck]

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]))

Any help would be greatly appreciated.

Thanks!

[v-lionel-msft]

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]

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.

[nleuck]

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

[v-lionel-msft · 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.