Filter Only some Collumns with Calculate

[areias_br]

Hi,

I have a measurement with the following Formula:

AcoesOverduePorDia =
VAR DataReferencia = MAX('tb_calendario'[Data])
RETURN
IF(
    DataReferencia <= TODAY(),
    CALCULATE(
        COUNTROWS(
            FILTER(
                ALL('Tabela_Ações'),
                'Tabela_Ações'[Data de Expiração] < DataReferencia &&
                (
                    ISBLANK('Tabela_Ações'[Data de Fechamento]) ||
                    'Tabela_Ações'[Data de Fechamento] > DataReferencia
                )
            )
        ),
        REMOVEFILTERS('tb_calendario'),
        KEEPFILTERS(VALUES('Tabela_Ações'[Task Owner])),
        KEEPFILTERS(VALUES('Tabela_Ações'[ActionType]))
    )
)

But it's not working. I need the filter 'tb_calendario' to continue to work. So, I should not filter the date. However, I still want to be able to filter per Task Owner or Action Type.

How could I fix the formula?

[v-kpoloju-msft · Accepted solution]

Hi  @areias_br, 

Thank you for reaching out to the Microsoft fabric community forum. I tried out the scenario using your data from the community, and it worked for me. I just tweaked the Dax and the relationship, and everything went smoothly.

Relationship:

outcome:

I am also including .pbix file for your better understanding, please have a look into it.

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @areias_br,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @areias_br,

Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[v-kpoloju-msft]

Hi @areias_br,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @Shubham_rai955, @rohit1991, @kushanNa, for those inputs on this thread.

Has your issue been resolved? If the response provided by the community member @Shubham_rai955, @rohit1991, @kushanNa, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

If the issue is not resolved, Could you please provide sample data illustrating the issue and the expected outcome? This will help us efficiently reproduce the scenario and assist you accordingly.

Thank you for using the Microsoft Fabric Community Forum.

[Shubham_rai955]

To allow your measure to respect filters on Task Owner and Action Type, while ignoring only the date filter from 'tb_calendario', you should remove only the date column filter— not the whole calendar table—so that other slicers from 'tb_calendario' (like month, year, etc.) will still work as usual.​

Revised Measure

AcoesOverduePorDia = VAR DataReferencia = MAX('tb_calendario'[Data]) RETURN IF( DataReferencia <= TODAY(), CALCULATE( COUNTROWS( FILTER( ALL('Tabela_Ações'), 'Tabela_Ações'[Data de Expiração] < DataReferencia && ( ISBLANK('Tabela_Ações'[Data de Fechamento]) || 'Tabela_Ações'[Data de Fechamento] > DataReferencia ) ) ), REMOVEFILTERS('tb_calendario'[Data]), KEEPFILTERS(VALUES('Tabela_Ações'[Task Owner])), KEEPFILTERS(VALUES('Tabela_Ações'[ActionType])) ) )

• Change REMOVEFILTERS('tb_calendario') to REMOVEFILTERS('tb_calendario'[Data]) in the CALCULATE section.​

• This ensures only the Date field filtering is removed, while other table filters are still applied.

This adjustment keeps your visual filters (including Task Owner, ActionType, and other calendar fields except date) working as expected.

[areias_br]

Hi,
it didn't work also.

Below you see the graph of the measure per week. It allows me to see if I'm improving or not related to treating overdue actions. It's a picture of that week, so, even if a action expires in week 36, but it was closed on week 39, it will count as overdue in week 36, 37 and 38.

However, I need the graph to react to the filters from the collumns: Task Owner and Action Type. But none of the formulas i've tried works, it always also change the calendar filter and them becomes a mess.

[kushanNa]

hi @areias_br 

Is it possible for you to provide sample test data / pbix file , the output you get right now, and the expected output — either in an Excel file or as a drawing?

[areias_br]

Hi,

Please see the sample data and pbix file.

The graph is with what I expect, however, if I filter by task owner, the graph doesn't change.

Power BI file
Database 

[rohit1991]

Hi @areias_br 

The issue happens because you’re using REMOVEFILTERS('tb_calendario'), which clears your date context. That’s why the calendar filter stops working.

If you want the date filter from 'tb_calendario' to stay active and still filter by Task Owner and Action Type, just remove that line.
Here’s the corrected version:

AcoesOverduePorDia =
VAR DataReferencia = MAX('tb_calendario'[Data])
RETURN
IF(
    DataReferencia <= TODAY(),
    CALCULATE(
        COUNTROWS(
            FILTER(
                ALL('Tabela_Ações'),
                'Tabela_Ações'[Data de Expiração] < DataReferencia &&
                (
                    ISBLANK('Tabela_Ações'[Data de Fechamento]) ||
                    'Tabela_Ações'[Data de Fechamento] > DataReferencia
                )
            )
        ),
        KEEPFILTERS(VALUES('Tabela_Ações'[Task Owner])),
        KEEPFILTERS(VALUES('Tabela_Ações'[ActionType]))
    )
)

This way:

• The calendar filter keeps working as expected.

• You can still filter by Task Owner and Action Type.

[areias_br]

Hi,

Didn't work. Still not filtering.

[rohit1991]

HI @areias_br 

To make the measure return the correct result while keeping the calendar filter active, you can slightly adjust the DAX logic. The key is to preserve the date context from tb_calendario and only remove filters within 'Tabela_Ações' except for Task Owner and Action Type.

AcoesOverduePorDia =
VAR DataReferencia = MAX('tb_calendario'[Data])
RETURN
IF(
    DataReferencia <= TODAY(),
    CALCULATE(
        COUNTROWS(
            FILTER(
                ALLEXCEPT(
                    'Tabela_Ações',
                    'Tabela_Ações'[Task Owner],
                    'Tabela_Ações'[ActionType]
                ),
                'Tabela_Ações'[Data de Expiração] < DataReferencia &&
                (
                    ISBLANK('Tabela_Ações'[Data de Fechamento]) ||
                    'Tabela_Ações'[Data de Fechamento] > DataReferencia
                )
            )
        )
    )
)

How this measure helps:

• Keeps your calendar date filter active in visuals.

• Counts actions that are overdue (expiration before the selected date) and still open.

• Allows filtering by Task Owner and Action Type without breaking the logic.