Though DAX calculation

[Anonymous]

Hi All,

I could use some help calculating the following measure.

So I have 3 tables containing a lot of data.

• Customer
• Leads
• Sales

The goal of my measure is to calculate the total conversion from lead to sale.

Note that my relationship is only set-up as followed :

So a customer can have multiple leads and multiple sales.

I would need to calculate the number leads that have been qualified, meaning have a sale that is generated after the lead creation date and not later then 90 days after the creation date.

My main difficulty is the many to many kind of this calculation! A column or measure, both would be fine!

Hope someone can help me out! 😊

[v-jingzhang]

Hi @Anonymous 

Can you provide some sample data and expected result to help us understand it better? As you said, a customer can have multiple leads and multiple sales, so how can we know which leads are related to which sales?

Best Regards,
Community Support Team _ Jing

[amitchandak]

@Anonymous , You can create a new column in lead like

Has sales = var _1 = countx(filter(sales, lead[customer id] = sales[customer ID] && Sale[Sales Date] >= Lead[Lead Date]  && Sale[Sales Date] <= Lead[Lead Date] +90 ), Sales[Sales ID])

Return

if(isblank(_1), false(), True())