Forum Discussion

areias_br's avatar
areias_br
Helper I
10 months ago
Solved

Filter Only some Collumns with Calculate

Hi, I have a measurement with the following Formula:   AcoesOverduePorDia = VAR DataReferencia = MAX('tb_calendario'[Data]) RETURN IF( DataReferencia <= TODAY(), CALCULATE( COUN...
Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    9 months ago

    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.

rohit1991's avatar
rohit1991
Super User
10 months ago

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's avatar
areias_br
Helper I
10 months ago

Hi,

Didn't work. Still not filtering.

  • rohit1991's avatar
    rohit1991
    Super User
    10 months ago

    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.