cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RykerZyker
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.

1 ACCEPTED SOLUTION

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] ) )
RETURN
    IF ( EventDate IN ContactDates, 1, 0 )

 

View solution in original post

16 REPLIES 16
AlexisOlson
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:


DimContacts:

ContactIDName
1joe
2jen

 

Fact Table: 

ContactIDDate
106/12/2021
103/12/2021
103/12/2021
209/11/2021
209/11/2021
208/11/2021

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] ) )
RETURN
    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?

RykerZyker_0-1638757123170.png

 

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: https://drive.google.com/drive/folders/1rbiwIWXHzezJzYBged-Rbu0cPjNjhi2K?usp=sharing

 

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.

AlexisOlson_0-1638890314084.png

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.
https://drive.google.com/drive/folders/1rbiwIWXHzezJzYBged-Rbu0cPjNjhi2K?usp=sharing


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!

RykerZyker
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

Announcements
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.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

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

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors