Removefilters not working on a column in the date table

[Anonymous]

Dear Gurus,

I was trying to use removefilters on the 'Short Month' column. I was expecting the same result which is 1/12/2022 on every single row. but the result is as below.

However, when I change the column to 'Month Number', it works as expected.

Any idea why that is? thanks

The date table is formulared as below.

[Anonymous · Accepted solution]

Hi @Anonymous ,

As far as I know, this result should be caused by [Month Number] column in your visual. You add [Month Number] in Matrix Row Field. So [Month Number] should be the key column, you must add  [Month Number] in REMOVEFILTER function to achieve your goal. There is no [Short Month] in Matrix, so remove filter based on [Short Month] won't work.

Ex:

Measure1 = CALCULATE(LASTDATE('Table'[Date]),REMOVEFILTERS('Table'[Month Number]))

Measure2 = CALCULATE(LASTDATE('Table'[Date]),REMOVEFILTERS('Table'[Short Month]))

I can reproduce your problem, when only add [Month Number], [Measure 1] show remove filter result and [Measure 2] only show max date in each month.

If I replace [Month Number] by [Short Month], I will get the same problem. So we must add key columns to remove filter.

And I find that if we sort [Short Name] by [Month Number], key column will be both [Short Name] and [Month Number] when we only add [Short Name] to Matrix Row. Then we need to use below code to get result.

Measure3 = CALCULATE(LASTDATE('Table'[Date]),REMOVEFILTERS('Table'[Month Number],'Table'[Short Month]))

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks Rico for your explanation. I still have one doubt though.

You mentioned I need to add short month as the 'key column' which I already did. see this 

Not sure how you achieved the result on yours. That is what I wanted to achieve.

[Anonymous]

Hi @Anonymous ,

Here [Short Month] is not sorted by [Month Number], so we will see it is sorted as text. Just add this column in matrix row. If you sort  [Short Month] by [Month Number], you need measure3.

I will attatch my sample file and I hope it could help you.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

thank you !! got it now

[PC2790]

In the Date Table, you have formatted ShortMonth as text and Month Number is still considered as retreived value form Date.

so when you use REMOVEFILTERS on Short Month, Power BI is not able to interpret the command and hence doesn't show the required result.

However the Month Number works due to the datatype