topic Re: Calculated Column from another fact table without relationship in DAX Commands and Tips

Calculated Column from another fact table without relationship

DIACHROMA — Wed, 08 Jun 2022 11:29:39 GMT

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 🙂

Thank you in advance for your precious help!

Pauline

Re: Calculated Column from another fact table without relationship

CNENFRNL — Wed, 08 Jun 2022 14:15:38 GMT

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

Re: Calculated Column from another fact table without relationship

DIACHROMA — Wed, 08 Jun 2022 14:50:17 GMT

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.

Re: Calculated Column from another fact table without relationship

vapid128 — Wed, 08 Jun 2022 21:38:37 GMT

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 ]

LINK THOSE 2 INDEX

Re: Calculated Column from another fact table without relationship

GuillaumeJ — Thu, 09 Jun 2022 09:39:35 GMT

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

Re: Calculated Column from another fact table without relationship

Anonymous — Thu, 09 Jun 2022 14:01:57 GMT

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

)

Re: Calculated Column from another fact table without relationship

Christophe93 — Mon, 20 Jun 2022 09:13:31 GMT

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

Re: Calculated Column from another fact table without relationship

v-cazheng-msft — Mon, 13 Jun 2022 03:32:42 GMT

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

Re: Calculated Column from another fact table without relationship

parry2k — Sat, 18 Jun 2022 02:36:26 GMT

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.