topic Re: All not removing filter context? Help in DAX Commands and Tips

All not removing filter context? Help

campelliann — Wed, 02 Feb 2022 19:05:03 GMT

Hi friends,

The more I think I know about dax, the more I realize this is hell on earth :). Help me understand this please.

So I used the Dax Below to get the distinct number of months with records on our timesheet, before the current month. I am basically counting the distinct values on "YearMonth" column.
The thing is that I am using a KPI card, which has a month filter context. Because we are in february, the Table Relatório colapses to a blank register with the measure below. Calendar Table filters Relatório Table.

The result below "should be" 1, because previous to this month there is only January. Now, here is the important part, because I am using ALL (Relatorio), I thought I was getting rid of all the filters in this table - but apparently I am not! Since the problem was solved when I embraced the DAX below with Calculate, expression, Remove filters())

Expression =

COUNTROWS (

DISTINCT (

SELECTCOLUMNS (

FILTER (

ALL ( 'Relatório' ),

'Relatório'[Date] <= LastMonth && 'Relatório'[Date]>=date(year(today()),01,01)

&& 'Relatório'[Empresa] IN VALUES ( 'Relatório'[Empresa] )

"AnoMes", 'Relatório'[YearMonth]

)

Re: All not removing filter context? Help

ValtteriN — Wed, 02 Feb 2022 19:19:25 GMT

Hi,

The problem is likely caused by this part of your DAX:

&& 'Relatório'[Empresa] IN VALUES ( 'Relatório'[Empresa] )

Now the table expression VALUES ( 'Relatório'[Empresa] ) is affected by filters -> if your filters cause this table to be blank it will in turn cause your calculation to fail.

Example of similar DAX:

Value-2 = CALCULATE(SUM('Dateadd-2'[Value]),filter(ALL('Dateadd-2'),'Dateadd-2'[Date] in VALUES('Dateadd-2'[Date])))

By slicing the date VALUES('Dateadd-2'[Date]) will return a blank table and thiss will cause everything else to be blank:

If we use ALL instead of VALUES the problem is gone:

Value-2 =

CALCULATE(SUM('Dateadd-2'[Value]),filter(ALL('Dateadd-2'),'Dateadd-2'[Date] in ALL('Dateadd-2'[Date]))

)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: All not removing filter context? Help

campelliann — Wed, 02 Feb 2022 19:33:53 GMT

Still trying to digest the information. But I sort of understand. And the problem was indeed the the part you mention. Genius!