Salesforce data best practice

[Broly]

Hi,

I'm a beginner in power Bi and needed some help.

I am importing three tables from Salesforce : Opportunities, Product and LineItemSchedule. An opportunity can have several products. A product can have several delivery schedules. Example, a product can have 10 in quantity and be delivered 5 in january and the 5 remaining in february. so in the Line item schedule for this product id we will have two lines : the "scheduled date" would be january 01 2022 and february 01 2022.

As you can imagine opportuny have a relationship with the product table and the product table has a relationship with the lineItemSchedule table. Obviously LineItemschedule has more rows (100 000) than Product (3000) and opportunities (700).

I want to display informations in a simple table but only one line per opportunity. I want to display the id of the opportunity and then the number of "scheduled dates" (delivery dates) that are in the past (date < today) for each opportunity.

What is the best practice for this ? should I just take the LineItemSchedule table and merge it with the other ones to bring all columns infomation about each opportunities and product and then just displaying it by summarizing the number of LineItemID for each opportunities ? 

Or should I create a measure calculating it ? Like this : 

SUMX(Opportunity, IF(CALCULATE(COUNT('Line Item Schedule'[Id]),'Line Item Schedule'[ScheduleDate]<TODAY())>0,COUNT('Line Item Schedule'[Id]),0))

Thanks in advance

[Broly]

Thank you, it works fine. When I have different levels of granularity should I always create measure instead of merging tables and creating calculated columns ?

[amitchandak]

@Broly , Try with this small change

new measure =
var _1 =CALCULATE(COUNT('Line Item Schedule'[Id]),'Line Item Schedule'[ScheduleDate]<TODAY())
return
SUMX(Opportunity, calculate(IF(_1>0,COUNT('Line Item Schedule'[Id]),0)) )

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.