cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Broly
Frequent Visitor

Salesforce data best practice

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
2 REPLIES 2
Broly
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 ?

amitchandak
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())
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.

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors