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
Anonymous
Not applicable

If contains function with a filter

Hi,

I've got a holiday table with dates (like xmas) which is an import of SQL and I've got a own created date table (with weekdays, month, date from Date Dimension table). Now I want to know which date is an holiday. I've did this with 

 

 

Holiday = IF(CONTAINS(CalendarEntry;CalendarEntry[StartTime];'Date'[Date]);"Yes";"No")

 

That works fine but I've got a problem that I've got Dutch and German Holidays in the same table (in 'CalendarEntry'[StartTime]). So I need to filter them ... The name (text) I can use is in CalendarEntry'[ModifiedBy] but I've tried a lot but no succes 😞

 

Anyone?

Because of my regional settings I need to use ; instead of ,

1 ACCEPTED SOLUTION
dedelman_clng
Community Champion
Community Champion

Try

 

Holiday =
IF (
    CONTAINS (
        FILTER ( CalendarEntry; CalendarEntry[ModifiedBy] = "German" );
        CalendarEntry[StartTime]; 'Date'[Date]
    );
    "Yes";
    "No"
)

 

Hope this helps
David

View solution in original post

2 REPLIES 2
dedelman_clng
Community Champion
Community Champion

Try

 

Holiday =
IF (
    CONTAINS (
        FILTER ( CalendarEntry; CalendarEntry[ModifiedBy] = "German" );
        CalendarEntry[StartTime]; 'Date'[Date]
    );
    "Yes";
    "No"
)

 

Hope this helps
David

Anonymous
Not applicable

Hi David,

 

Thnx for your reply

I've tried the folowing code (copy of your code and replace the filter value)

Holiday = 
IF (
    CONTAINS (
        FILTER ( CalendarEntry; CalendarEntry[ModifiedBy] = "Esther");
        CalendarEntry[StartTime]; 'Date'[Date]
    );
    "Yes";
    "No"
)


And it works 🙂 Thnx for helping me!

I've made a picture of the result (for privacy, I've stripped the surnames and I've create some slicers to reduce the results)

Holiday_formula.png

 

 

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.