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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Salesforce data best practice


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
Frequent Visitor

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 ?

Super User
Super User

@Broly , Try with this small change


new measure =
var _1 =CALCULATE(COUNT('Line Item Schedule'[Id]),'Line Item Schedule'[ScheduleDate]<TODAY())
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.

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.