cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
campelliann
Resolver I
Resolver I

All not removing filter context? Help

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]
)
)
1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

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])))
 
ValtteriN_0-1643829238422.png

 

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

ValtteriN_1-1643829292457.png

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]))
)
ValtteriN_2-1643829554765.png

 

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/

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ValtteriN
Super User
Super User

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])))
 
ValtteriN_0-1643829238422.png

 

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

ValtteriN_1-1643829292457.png

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]))
)
ValtteriN_2-1643829554765.png

 

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/

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors
Top Kudoed Authors