Help with DAX

[dandamudisanjay]

Hi All, 

Hope you are all safe and well. 

Just want some help with a DAX please as follows. 

I'm creating a dashboard with lead start date from Leads entity and want total distinct customers from Customers table to be displayed who have start date >= Opportunity close date. 

Also, want to get purchase amount total for the customers to be available as well. 

So, based on the lead start date filter, the respective opportunities who are having link to Account which is connected to customers through service table needs to be displayed. 

The relationship from power bi model is as follows. 

Hope to hear from you. 

Regards

Sanj

[lbendlin]

So you are trying to catch the people who put bogus data in the leads object?  Usually a lead predates the opportunity creation (let alone the closure).

Please explain the rationale for the bidirectional links. Aren't Accounts and Customers the same thing? 

What do you consider dimensions, and what do you consider facts?

Have you tried to create a snowflake schema?

[dandamudisanjay]

Hi @lbendlin , 

Accounts and customers are slightly different. Imagine Accounts as supplier and customers is actual customers and our business is a re seller.  

Spot on with leads and opportunities. We only want to count total customers and total sales amount from customer entity once an opportunity is closed and only those where the date customer joined is greater than opportunity closed date. 

Rationale behing the bidirectional link is so it will filter out those accounts based on the lead selection. 

We havent tried the snowflake schema yet. 

Regards