Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

If a date equals x from another table then 1 else 0

I need help figuring out if a person in my dataset was present on a specific day. 

The relevant table are the Fact Table and a dimension table (dimContacts). I would like to do this using a custom column or a measure, if possible both. 

I have a 1 * Many cardinality. 

(When I try to do this with an if statement I don't get an option to choose a column from another table, I also tried using the Relate function but couldn't really get that to work.)


Any help is much appreciated.


So, something like this as a calculated column?

DimContacts[PresentOnEventDate] =
VAR EventDate = DATE ( 2021, 6, 30 ) /*whatever fixed date*/
VAR ContactDates = CALCULATETABLE ( VALUES ( FactTable[Date] ) )
    IF ( EventDate IN ContactDates, 1, 0 )


View solution in original post

Super User
Super User

What table are you trying to add a calculated column to?


What would a visual with this measure look like?

I am trying to add this to the dimContacts table. 

I would be using this to measure to see how many people were present on that day and eventually if those same people were present 7 days later then 14 days later etc. This would essentially become like a linechart or something that would show the dropoff overtime. 

TotalWeek 1Week 2Week 3
Campaign 11501009510
Total (On The Day)150200250200
% Change100%50%38%5%

For it to be a calculated column on dimContacts, either that table must have multiple rows per contact (for multiple dates), or else you can only check if that contact was present on one specific date (which can't be dynamically selected by a slicer since calculated columns can be dynamic this way).

I only have one row for a specific person and I only need results for just that day as this day is an event and I don't need this to be affected by any slicers . 


This is what the structure kinda looks like:




Fact Table: 


So, something like this as a calculated column?

DimContacts[PresentOnEventDate] =
VAR EventDate = DATE ( 2021, 6, 30 ) /*whatever fixed date*/
VAR ContactDates = CALCULATETABLE ( VALUES ( FactTable[Date] ) )
    IF ( EventDate IN ContactDates, 1, 0 )


Also, since I want to know if the same person was onsite 7 days later would you recommend a measure or a custom column for this? 
I will end up with about 10-15 additional columns for all the diffrent events so, just curious what best way to go about it would be.

A measure can be a bit more flexible. You could probably write one instead of 10-15 since the EventDate variable doesn't have to be a fixed constant in a measure (it can depend on a slicer or local filter context).

The chart below is what i am trying to achive, I want to have a slicer where I can look at diffrent events and the values for the week and present (total count for that day) would change. 


Could this be achived with just a single measure?



I don't really understand what any of the numbers mean but I don't think you should need more than three measures tops (Campaign, Total, % Change).

Campaign 1 is the total count of individuals present that attended the event/campaign. The total below that is the general count of people on site.
I should be able to do the measures for the total and the % change but I am still quite uncertain on how I would go about adding more campaigns with different dates in a measure.
Are you able to give me example with the following dates
Event 1: 06/12/2021
Event 2: 05/12/2021
Event 3: 01/01/2021 > 30/11/2021 (An event that ran for a month)

Suppose you have a table, Events, of events and dates like your examples. Then you could use the column Events[Event] as a slicer on your report page and instead of a hardcoded date, you could write

VAR EventDate = SELECTEDVALUE ( Events[Event] )

within the measure I gave.


This should also work if you want to use Events[Event] on the columns/rows of a matrix visual.

I tried this and created a new table with the campaign name and dates, changed the measure to the new one, but I still can't seem to get the result I would like. For now, let's just say I need to get a total count for different events using the slicer and then another column 7 days later which would show how many of the same people are present.

I created a sample PBI file and replicated my steps, are you able to see what I might be doing wrong:


I'm not sure how 7 days later works for a multi-day event.


I've tinkered with your file a bit though. Give it a look.


Hello, sorry for replying so late, got busy.
I had a look at the PBI file and realised I probably explained what I wanted poorly. I pretty much need a total distinct count for how many people attended an event. for example if in event 1 there were 150 total entries and only 20 of them were unique, I would like the result to be:

 Distinct TotalDistinct Total | 7D later
Event 1205 (from event 1)
Distinct Total 2050 (on this day)

The '7 day later' for mutiple dates can just look a the last day in that duration and do a +7 on that date.
This is the PBI file with a few changes and a table with horrible Dax to roughly show what I kinda need.

I should also mention the main goal is of all of this is to pretty much look at 1 event at a time and look at the drop off for the people that attended that event over time. The 'over time' is simply just the 7 days after a measure or 14 etc.

Yup, this is it. Thanks for your help, been struggling with this for so long... Finally haha, thanks!

Helper I
Helper I

I also have a date table that is linked to my fact table and is marked as the date table. 

Helpful resources

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors