Not able to isolate a condition in FILTER() POWERBI DAX

[paulos]

Backlog = 
VAR CreatedIncidents =
    CALCULATE(
        COUNT(Tabla_INC[Opened_WithoutTime]),
        FILTER(
            ALL(Tabla_INC),
            Tabla_INC[Opened_WithoutTime] <= MAX(Tabla_Fechas_Incidencias[Date]) &&
            Tabla_INC[Assignment_Group] = "N1 SYSTEMS"
        )
    )

VAR ClosedIncidents =
    CALCULATE(
        COUNT(Tabla_INC[Closed_WithoutTime]),
        FILTER(
            ALL(Tabla_INC),
            Tabla_INC[Closed_WithoutTime] <= MAX(Tabla_Fechas_Incidencias[Date]) &&
            Tabla_INC[Assignment_Group] = "N1 SYSTEMS"
            -- TRYING TO PUT Tabla_INC[Status] <> "Cancelled" BUT IT DESTROYS EVERYTHING; ONLY SOLVED IF I PUT THIS CONDITION IN EVERY VAR BUT THAT'S NOT POSSIBLE AS ITS NOT CORRECT!!
        )
    )

VAR ResolvedCases =
    CALCULATE(
        COUNT(Tabla_INC[Resolved_WithoutTime]),
        FILTER(
            ALL(Tabla_INC),
            ISBLANK(Tabla_INC[Closed_WithoutTime]) &&
            Tabla_INC[Assignment_Group] = "N1 SYSTEMS"
        )
    )

VAR Result = CreatedIncidents - ClosedIncidents - ResolvedCases

can someone explain me how to put the Tabla_INC[Status] <> "Cancelled" without breaking the whole backlog?

As I said in the comment if I put the condition Tabla_INC[Status] <> "Cancelled" in all the VARs then it works but that's not a good option as CreatedIncidents and SolvedIncidents can contain no problem Tabla_INC[Status] <> "Cancelled"; the first one (CreatedIncidents) because you MUST count the incidents that were opened even if they were later on cancelled and the ResolvedCases because there won't be ever that such option so you can avoid doing that checking.

[v-nmadadi-msft · Accepted solution]

Hi @paulos ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.


How to Get Your Question Answered Quickly - Microsoft Fabric Community

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you

[v-nmadadi-msft]

Hi @paulos 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution otherwise please try to share some sample data so that we can take a look. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[paulos · Accepted solution]

Hello, no, I didn't solve it. I just avoided the problem. I won't share more information that could help solve the problem. I finally decided it didn't made sense to not count cancelled anyways for closed as it's closed anyways. And my question was in usable format; I didn't share as a screenshot but rather as a piece of code. I can mark your answer as the final solution, I don't mind or you can just close it and archive it. Whatever the politics of the forums are. Thank you. 

[v-nmadadi-msft]

Hi @paulos ,

We really appreciate your efforts and for letting us know the update on the issue. Apologies that you were not able to completely resolve the issue.

For any other issues related to Fabric and Power BI, please continue using fabric community forum for your further assistance.

Thank you

[v-nmadadi-msft · Accepted solution]

Hi @paulos ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.


How to Get Your Question Answered Quickly - Microsoft Fabric Community

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you

[paulos]

@Fowmy that doesn't work. That's what ChatGPT says too. It completely destroys the backlog because that condition is only applied to ClosedIncidents and the other `VAR` are clearing the whole table meanwhile. 

[Fowmy]

@paulos 

In your current code, you are clearing the whole table, instead, target on the columns that you need to chagne the filter:

VAR ClosedIncidents =
    CALCULATE(
        COUNT(Tabla_INC[Closed_WithoutTime]),
        Tabla_INC[Closed_WithoutTime] <= MAX(Tabla_Fechas_Incidencias[Date]),
        Tabla_INC[Assignment_Group] = "N1 SYSTEMS",
        Tabla_INC[Status] <> "Cancelled"
    )