CODEWITHSUNDEEP
powerbidax
JStewart
JStewart

Reputation: 5

PowerBI / DAX - REMOVEFILTERS not working as expected

We are using a date table based on a fiscal calendar. I need to determine if a particular year has 53 weeks or 52 weeks.

The page has a slicer with a date hierarchy that is used to select a year, quarter, month, or week. I would like the Measure to return the maximum number of weeks in a particular year regardless of what is selected.

The measure below works fine, it returns 53 which is the maximum number of weeks in any year regardless of the selection in the date hierarchy slicer.

PriorYearNumberOfWeeks = calculate(max(vwPBIDate[Week of Year]),REMOVEFILTERS(vwPBIDate))

However, when I try to add a filter condition to select the proper year it no longer works it returns the maximum week number of the period selected in the hierarchy. If I select January (a 5 week month) it returns 5 instead of 53 then number weeks in fiscal 2021.

PriorYearNumberOfWeeks = calculate(max(vwPBIDate[Week of Year]),REMOVEFILTERS(vwPBIDate),filter(vwPBIDate,vwPBIDate[Fiscal Year Number]=2021))

Upvotes: 0

Views: 573

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9092

You're missing an ALL:

=
CALCULATE(
    MAX( vwPBIDate[Week of Year] ),
    REMOVEFILTERS( vwPBIDate ),
    FILTER(
        ALL( vwPBIDate ),
        vwPBIDate[Fiscal Year Number] = 2021
    )
)

which is equivalent to:

= 
CALCULATE(
    MAX( vwPBIDate[Week of Year] ),
    REMOVEFILTERS( vwPBIDate ),
    vwPBIDate[Fiscal Year Number] = 2021
)

Upvotes: 1

Related Questions