Measure not applying the filters on all pages -

[SusanaSantos]

This is the measure I have (fields are part of the visual) but on the previous year calculation is not considering the filters on all pages. (those filters are not present in the visual)

Valor_PY_Week =

VAR CurrentYear = SELECTEDVALUE('table'[Ano])

VAR CurrentWeek = SELECTEDVALUE('table'[Semana])

RETURN

CALCULATE(SUM('table'[Valor]), FILTER(ALL('table'), 'table'[Ano] = CurrentYear - 1 && 'table'[Semana] = CurrentWeek && 'table'[Transitario.Sst Forwardermtc] IN VALUES('table'[Transitario.Sst Forwardermtc]) && 'table'[Tipo_carga] IN VALUES('table'[Tipo_carga])))

[v-sgandrathi · Accepted solution]

Hi @SusanaSantos,

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 provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

[v-sgandrathi]

Hi @SusanaSantos,

Just checking in to see if you had a chance to follow up on our earlier conversation. If you're still encountering the issue, please share the sample data so we can assist you with an accurate solution.

If you have any further questions, feel free to reach out anytime.

Thank you.

[v-sgandrathi]

Hi 
Thank you for engaging with the Microsoft Fabric Community Forum.

Try this DAX Measure:

Valor_PY_Week =
VAR CurrentYear = SELECTEDVALUE(SalesData[Ano])
VAR CurrentWeek = SELECTEDVALUE(SalesData[Semana])
RETURN
CALCULATE(
SUM(SalesData[Valor]),
FILTER(
ALLSELECTED(SalesData),
SalesData[Ano] = CurrentYear - 1 &&
SalesData[Semana] = CurrentWeek
)
)

Additionally, I have included the PBIX file that I created using the sample data. Kindly review it and confirm whether it aligns with your expectations.

Thank you.

[SusanaSantos]

Hi, using this measure brings no values, up till now the measure that brings values but does not consider the filters on all pages is this measure

Valor_PY_Week3 = VAR CurrentYear = MAX('table'[Ano]) VAR CurrentWeek = MAX('table'[Semana]) RETURN SUMX(ADDCOLUMNS(VALUES('table'[Tipo_carga]), "PY_Valor", CALCULATE(SUM('table'[Valor]), FILTER(ALL('table'),'table'[Ano] = CurrentYear - 1 && 'table'[Semana] = CurrentWeek && 'table'[Transitario.Sst Forwardermtc] IN VALUES('table'[Transitario.Sst Forwardermtc]) && 'table'[Tipo_carga] = EARLIER('table'[Tipo_carga])))), [PY_Valor])

[v-sgandrathi]

Hi @SusanaSantos,

Try this DAX Measure:

Valor_PY_Week_Fixed =

VAR CurrentYear = MAX(SalesData[Ano])

VAR CurrentWeek = MAX(SalesData[Semana])

RETURN

    CALCULATE(

        SUM(SalesData[Valor]),

        REMOVEFILTERS(SalesData[Ano], SalesData[Semana]),

        SalesData[Ano] = CurrentYear - 1,

        SalesData[Semana] = CurrentWeek

    )

Thank you.

[v-sgandrathi]

Hi @SusanaSantos,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

Thank you.

[v-sgandrathi]

Hi @SusanaSantos,

As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?

Thank you.

[SusanaSantos]

HI, sorry I did not reply to you but had no time yet to check last measure. Will revert as soon as I try again.

[SusanaSantos]

Just tried it and once again it does not bring any values. 

Really cannot find out what i am doing wrong !

[MasonMA]

Could you share a sample file or some data in excel with tables and relationships descriptions? 

It's hard to for other users to help you debug measures without using these data. 

[SusanaSantos]

it does not bring any values

[MasonMA]

@SusanaSantos 

Hi, ALL removes all the filters from 'table'. You may need to use REMOVEFILTERS and KEEPFILTERS in this situation to removes only year and week filters and keeps all other slicer/page/report filters intact

Valor_PY_Week = 
VAR CurrentYear = SELECTEDVALUE('table'[Ano])
VAR CurrentWeek = SELECTEDVALUE('table'[Semana])

RETURN
CALCULATE(
    SUM('table'[Valor]),
    REMOVEFILTERS('table'[Ano], 'table'[Semana]),

    KEEPFILTERS('table'[Ano] = CurrentYear - 1),
    KEEPFILTERS('table'[Semana] = CurrentWeek)
)

[SusanaSantos]

does not bring any values

[DataNinja777]

Hi @SusanaSantos ,

Your measure isn't applying the page-level filters because the ALL('table') function is removing them. The ALL() function strips away the entire filter context from the specified table, which includes any slicers or filters applied on the report page.

To fix this, you should only remove the filters from the specific columns you need to change (Ano and Semana), while preserving all other external filters. The corrected measure below uses REMOVEFILTERS to achieve this.

Valor_PY_Week =
VAR CurrentYear = SELECTEDVALUE('table'[Ano])
VAR CurrentWeek = SELECTEDVALUE('table'[Semana])
RETURN
CALCULATE(
    SUM('table'[Valor]),
    REMOVEFILTERS('table'[Ano], 'table'[Semana]),
    'table'[Ano] = CurrentYear - 1,
    'table'[Semana] = CurrentWeek
)

This version works correctly because CALCULATE starts with the full filter context, including your page-level filters. The REMOVEFILTERS function then precisely targets and removes only the existing filters on the Ano and Semana columns. Immediately after, new filters are applied for the previous year and the correct week. This process ensures that all other filters, like those on Transitario.Sst Forwardermtc and Tipo_carga, remain active, making your original IN VALUES() conditions unnecessary.

Best regards,