topic Help with ignoring filters on calculating a backlog in DAX Commands and Tips

Help with ignoring filters on calculating a backlog

MGASP01 — Fri, 18 Oct 2024 09:21:04 GMT

Hi all,

I need to create a measure that calculates a work backlog.

Since it's a backlog, I don't want the result to be affected by any filter the user could apply by using a date slicer on the same page.

So I have two tables, bl_next and bl_next_calendar.

bl_next has 4 columns: case_id, status, date, market.

bl_next_calendar has 3 columns: date, week, month.

There's a relationship between the tables on the date field.

This is what I've come up to:

backlog_count =

CALCULATE(

COUNTX(FILTER(bl_next, bl_next[status] <> "Closed" && bl_next[status] <> "Cancelled"), bl_next[case_id]),

REMOVEFILTERS(bl_next_calendar),

VALUES(bl_next[market])

)

Can anyone help me on this?

When the user filters the date slicer to a specific range of dates it affects the result and I don't want this to happen.

Thank you in advance.

Re: Help with ignoring filters on calculating a backlog

FreemanZ — Fri, 18 Oct 2024 09:27:06 GMT

hi MGASP01 ,

try like:

backlog_count =

CALCULATE(

COUNTX(FILTER(bl_next, bl_next[status] <> "Closed" && bl_next[status] <> "Cancelled"), bl_next[case_id]),

REMOVEFILTERS(bl_next_calendar),

REMOVEFILTERS(bl_next[date]),

VALUES(bl_next[market])

)

Re: Help with ignoring filters on calculating a backlog

forgetmenot — Fri, 18 Oct 2024 14:24:56 GMT

try this :

backlog_count =

CALCULATE(
    COUNTX(
        FILTER(
            bl_next, 
            bl_next[status] <> "Closed" && bl_next[status] <> "Cancelled"
        ), 
        bl_next[case_id]
    ), 
    ALL(bl_next_calendar), 
    VALUES(bl_next[market])
)

Re: Help with ignoring filters on calculating a backlog

MGASP01 — Mon, 21 Oct 2024 08:26:02 GMT

Thanks guys for your help.

Unfortunately, none of your tips have worked.

I can't figure out why, but filtering dates with the slicer is still messing up the results.
The only thing I could get to work was duplicating the bl_next table and keeping just the columns I needed, without linking it to anything else.

I know it's a terrible solution, but it's the best I could do.

Re: Help with ignoring filters on calculating a backlog

Rupak_bi — Tue, 22 Oct 2024 08:34:11 GMT

Hi,

Can you please share sample data and the desired output you are looking at? just to check why these solutions are not working. If the output is a separate visual, you may try switching off the visual interactions as well from the format pane.