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

[Anonymous]

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.

[shahidali]

I have two column with the name on latest publish date and plan last update.IF date is showing in latest publish date then output will come published and if date is not showing in latest publish date and date is showing in plan last update then outcome will come Templated Created if in both the column date is shwoing then output will come templated completed

[shahidali]

Hi

I have event date column with mentioned date1st jan 2024 to 31st dec 2024. Requirment is want to create another column if the date is coming from current year then  outcome will be green color and if date is beyond 31 december 2024. Example 1st jan 2025 then outcome will be orange color

[AlexisOlson]

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

What would a visual with this measure look like?

[Anonymous]

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. 

Total	Week 1	Week 2	Week 3
Campaign 1	150	100	95	10
Total (On The Day)	150	200	250	200
% Change	100%	50%	38%	5%

[AlexisOlson]

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

[Anonymous]

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:

ContactID	Name
1	joe
2	jen

Fact Table: 

ContactID	Date
1	06/12/2021
1	03/12/2021
1	03/12/2021
2	09/11/2021
2	09/11/2021
2	08/11/2021

[AlexisOlson · 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 )

[Anonymous]

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.

[AlexisOlson]

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

[Anonymous]

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?

[AlexisOlson]

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

[Anonymous]

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)

[AlexisOlson]

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.

[Anonymous]

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

[AlexisOlson]

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.

[Anonymous]

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 Total	Distinct Total | 7D later
Event 1	20	5 (from event 1)
Distinct Total	20	50 (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.

[Anonymous]

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

[Anonymous]

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