Forum Discussion
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:
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 🙂
Thank you in advance for your precious help!
Pauline
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
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
For fun only, a showcase of powerful Excel worksheet formula,
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.
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.
https://drive.google.com/file/d/19U2E8QPUBG9x_XzExM2lYHcpB4y-vPIf/view?usp=sharing
Regards
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))THENcreate index column your newTable and sale tableindex = [Value]&[Attendee ]LINK THOSE 2 INDEXHello,
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] )))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
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 ) )✨ Follow us on LinkedIn and to our YouTube channel
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.
