How to remove certain filters and slices but keep others

[frstrich]

Hi,

I have only one Table in Power BI Desktop, imported from an excel sheet, containing Data of support tickets called Tickets.

It's roughly shaped like this:

Ticket-ID	Created	Closed	Organisation
TIC00001	11.01.2022 11:18:01	11.01.2022 12:30:41	GF
TIC00002	11.01.2022 11:34:30		KA
TIC.....	18.07.2023 12:00:12	19.07.2024 08:14:30	FR
TIC29784	27.05.2024 06:50:18		MI

As you can see, if a ticket has not been closed yet, there is no date in the corresponding column.

I want to have a bar-chart, where the amount of tickets still open at the end of each month is displayed - filterable by Organisation, so I created a bar chart, plotting the Year and Month of Created as the x-axis, as well as the following measure:

OpenTickets = 
VAR SelectedDate = EOMONTH(MAX('Tickets'[Created].[Date]),0)
RETURN COUNTROWS(
    CALCULATETABLE(
        ALLSELECTED(Tickets),
        REMOVEFILTERS(Tickets[Created]),
        INT('Tickets'[Created]) <= INT(SelectedDate) && (INT('Tickets'[Closed]) >= INT(SelectedDate) || ISBLANK('Tickets'[Closed]))
    )
)

I use INT(date) because sometimes Power BI gets confused by the timestamp inside the Created or Closed datetime

Sadly this does not work as expected: If I select a year outside of the plot (either as a slicer or as a selection in another chart) the amount of open tickets gets reduced by the amount of tickets created in the years now out of scope. This is of course fixed by using ALL instead of ALLSELECTED, but then the chart does not change, when selecting different organisations (also either as a slicer or selection in another chart).

I also tried using COUNTROW(FILTER(ALLSELECTED(...))) but this does not work either.

Is there any way to achieve what I want?

Thanks

[mickey64]

Step 0: I use your data below.

Step 1: I add a 'Date Created' column and a 'Date Closed' column on Power Query Editor.

Step 2: I unpivot these column.

- After Unpivot -

Step 3: I add a 'Flag' column and change the type of the column.

Step 4: I make a calendar table and add a relationship.

Calendar = CALENDAR(FIRSTDATE('Tickets'[Value]),LASTDATE('Tickets'[Value]))

Step 5: I make a measure and a graph.

M_Flag = CALCULATE(SUM('Tickets'[Flag]),FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

[frstrich]

Thanks, I would have to restructure my layouts for this, but the idea is really interesting

[SamWiseOwl]

Hi @frstrich 

Is this a rough idea of what the issue looks like:

And when you tick a year:

[frstrich]

Precisely. If I select the year the amount of open tickets gets reduced, but the amount should not depend on the year selected, but should depend on the selected organisations

[SamWiseOwl · Accepted solution]

Hi @frstrich 

Like this?

Your code was correct, all you needed was to specify to remove the Year filter:

OpenTickets =

VAR SelectedDate =CALCULATE( EOMONTH(MAX('Tickets'[Created].[Date]),0), all('Tickets'[Created]))

RETURN COUNTROWS(

    CALCULATETABLE(

        ALLSELECTED(Tickets),

        REMOVEFILTERS(Tickets[Created].[Year]),

        INT('Tickets'[Created]) <= INT(SelectedDate) && (INT('Tickets'[Closed]) >= INT(SelectedDate) || ISBLANK('Tickets'[Closed]))

    )

)

[frstrich]

Oh wow, thank you very much. I wasn't aware that you had to specify the exact filter and the parent element isn't enough.