cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Calculated Column from another fact table without relationship

Hello dear colleagues,

I'm trying to create a calculated column in the following context:

I have an "Event Attendees" table in which I have a row for each attendee of promotional events. I need to know if within 30 days of the date of the event an order has taken place.

Regarding my model, I have my "Event Attendees" table, my "Sales" table but they have no relationship between them. They are both linked to my "Customer" and "Calendar" dimensions.

I've already created a new column that gives us the date at Days + 30, as you see below:

 Event Name Event Date Days +30 Attendee Sales D+30 ? Event 1 01/01/2022 31/01/2022 Customer A Event 1 01/01/2022 31/01/2022 Customer B Event 1 01/01/2022 31/01/2022 Customer C

I have already tried the following formula:

Sales D+30 =
CALCULATE (
[Total Sales] ,
FILTER ( 'Calendar' , 'Calendar'[Dates] >= MINX( 'Events Attendees' , 'Events Attendees'[EVENT DATE] ) ) ,
FILTER ( 'Calendar' , 'Calendar'[Dates] <= MINX( 'Events Attendees' , 'Events Attendees'[DAYS +30] ) )
)

But it is not working.

Do you have any idea how I can solve this? If my formula is correct, does the problem come from my model?

Any help or new suggestion will be welcome 🙂

Pauline

1 ACCEPTED SOLUTION
Community Support

Hi @DIACHROMA,

You may try this Measure.

``````Sales D+30? =
CALCULATE (
COUNT ( Sales[OrderDate] ), //Count Order in the Range
TREATAS ( VALUES ( 'Events Attendees'[Attendee] ), Sales[Customer] ),
FILTER (
'Calendar',
'Calendar'[Dates] >= MINX ( 'Events Attendees', 'Events Attendees'[EVENT DATE] )
&& 'Calendar'[Dates] <= MINX ( 'Events Attendees', 'Events Attendees'[DAYS +30] )
)
)``````

Then, the result will look like this.

Also, attached the pbix file as reference.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

8 REPLIES 8
Super User

@DIACHROMA it is pretty straightforward since you have the model already in place, just add the following measure:

``````Sales D+30 New? =
VAR __numberOfDays = 30
VAR __eventStartDate = MIN ( 'Events Attendees'[Event Date]  )
VAR __eventEndDate = __eventStartDate + __numberOfDays
RETURN
CALCULATE (
COUNTROWS ( Sales ),
DATESBETWEEN (
'Calendar'[Dates],
__eventStartDate,
__eventEndDate

)
)``````

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Community Support

Hi @DIACHROMA,

You may try this Measure.

``````Sales D+30? =
CALCULATE (
COUNT ( Sales[OrderDate] ), //Count Order in the Range
TREATAS ( VALUES ( 'Events Attendees'[Attendee] ), Sales[Customer] ),
FILTER (
'Calendar',
'Calendar'[Dates] >= MINX ( 'Events Attendees', 'Events Attendees'[EVENT DATE] )
&& 'Calendar'[Dates] <= MINX ( 'Events Attendees', 'Events Attendees'[DAYS +30] )
)
)``````

Then, the result will look like this.

Also, attached the pbix file as reference.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

Helper II

Hi maybe something like that

--- No relationships between 'Calendar' & 'Events Attendees' tables , just a dynamic filetring
--- But relationships between 'Calendar' & 'Sales' tables
Sales D+30 =
Var CurrentDate
= MAX('Calendar'[Dates]) RETURN
CALCULATE (
[Total Sales] ,
FILTER ( 'Events Attendees' , 'Events Attendees'[EVENT DATE] >= CurrentDate && 'Events Attendees'[DAYS +30] <=CurrentDate
)
)

Table visual ==>

Event name | Calendar'[Dates] | Sales D+30

Anonymous
Not applicable

Hello,

Is an attendee not supposed to be the 'customer' in the Sales table if he places an order ?

Have you tried using the AND operator instead of two 'FILTER' operations ?

Like this :

Sales D+30 =
CALCULATE (
[Total Sales] ,
AND( 'Calendar'[Dates] >=MINX( 'Events Attendees' , 'Events Attendees'[EVENT DATE]),
'Calendar' , 'Calendar'[Dates] <= MINX( 'Events Attendees' , 'Events Attendees'[DAYS +30] ))
)

Solution Specialist

Just Make a relationship between 2 tables.

And one important thing to remember, your sale table is your main table.

IF your event are all 30 days,

create a new table.

newTable = GENERATE('table',GENERATESERIES('table'[Event Date],'table'[Event Date]+30))
THEN
create index column your newTable and sale table
index = [Value]&[Attendee ]
Community Champion

For fun only, a showcase of powerful Excel worksheet formula,

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
New Member

Hello,

The key for me is to build an Event Table thanks to your attendee table.

Then, you can create a calculated Column to do the job.

Regards

Helper II

Hi @CNENFRNL thank you very much for this proposal !

However, in my Sales table I don't have the "Relevant Event" column.

I have events on one side, sales on the other, but no way to link an event to the sales generated behind it. It is for this reason that we look at 30 days following an event to find out if over this period the participant has generated sales.

Announcements

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

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

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors