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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.