cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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
Super User

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

16 REPLIES 16
Super User

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

What would a visual with this measure look like?

Anonymous
Not applicable

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%
Super User

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

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
Super User

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

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.

Super User

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

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?

Super User

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

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)

Super User

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

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

Super User

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

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.

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

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

Anonymous
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors