Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Uday02
Frequent Visitor

ISFILTERED and ISCROSSFILTERED on a Date Column

Hi All,

 

I am trying to understand how ISFILTERED  and ISCROSSFILTERED work with respect to a date column from the date table.

 

in the screenprint below,  the value in the ISCROSSFILTERED field returns true when I choose any Month from the Slicer. Yet even though it's a direct filter, the ISFILTERED measure doesn't change.


Measures: 

ISDatecoumnfilterd = ISFILTERED('Date'[Date])
ISDatecoumnCrossfilterd = ISCROSSFILTERED('Date'[Date])

 

Uday02_6-1680420729285.png

 

In another scenario, I added both measures to the same table visual, and the ISCROSSFILTERED now returns true, whereas the same in the card visual outside has not changed.

 

Uday02_3-1680419623297.png

 

The only time the value of the ISFILTERED field changes is when the Date column in the table's visual is changed to just the Date from Date hierarchy.

 

Uday02_5-1680419808169.png

 

This is extremely perplexing, and I feel as if I won't be able to continue my Power BI learning journey until I completely understand these basic concepts.

 

Please enlighten me.

 

Thank you in advance.

2 REPLIES 2
andhiii079845
Super User
Super User

Hi,

 

you ask good questions and you go a important step to understand DAX . I try to explain it: At first ISFILTERED(COLUMN) give back "true", if this column is directly filtered e.g. by a slicer. ISCROSSFILTERED(COULMN) give also back "true" if a filter from a another column has a effect to this column.

1)

In your first screenshot you filter the monthname column via the a slicer (July), so this is correct:

ISDatecoumnfilterd = ISFILTERED('Date'[Date]) -> FALSE (no direct filter from to the column) 
ISDatecoumnCrossfilterd = ISCROSSFILTERED('Date'[Date]) -> TRUE (yes, the filter from month name has a effect to the column). 
 
2)
In your second screenshot you bring bring both measure to the date hierarchy. This hierarchy not use the 'Date'[Date] column directy. So he same result as 1) is shown.
ISDatecoumnCrossfilterd is false in your card visual because you choose no month name or other filter that has a effect to 'Date'[Date]. 
3) 
No the "magic" happens, because you put 'Date'[Date] in the matrix visual. First the card visual stay both false, because no slicer effect to the date column. BUT in your matrix visual every row is filtered now by 'Date'[Date], so both measure are true! This one row is a filter itself for the 'Date'[Date]. More help and deeper view:  https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/




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

Proud to be a Super User!




I really appreciate you taking the time to explain this to me. While I'm still not completely clear on the logic, your explanation has definitely brought me closer to my desired understanding

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.