Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
DIACHROMA
Helper II
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 NameEvent DateDays +30Attendee Sales D+30 ?
Event 101/01/202231/01/2022Customer A 
Event 101/01/202231/01/2022Customer B 
Event 101/01/202231/01/2022Customer 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 

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
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.

vcazhengmsft_0-1655091102044.png

 

vcazhengmsft_1-1655091102046.png

 

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

View solution in original post

8 REPLIES 8
parry2k
Super User
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

    ) 
)

 

Follow us on LinkedIn and YouTube.gif 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.

 



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.

v-cazheng-msft
Community Support
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.

vcazhengmsft_0-1655091102044.png

 

vcazhengmsft_1-1655091102046.png

 

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

Christophe93
Helper II
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] ))
)

 



 

vapid128
Solution Specialist
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 ]
LINK THOSE 2 INDEX
CNENFRNL
Community Champion
Community Champion

CNENFRNL_0-1654697140410.png

CNENFRNL_1-1654697223912.png

 

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

CNENFRNL_0-1654697777247.png


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!

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

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors