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

Calculations on 2 fact Tables connected via dimension Table

Hi,

 

I need to calculate a latest price from a fact Table (UnderlyingDaily), but in my visual I need to place dimension fields from another fact Table. My current "Price Latest" measure only works if I use "Symbol" Field from "Dim_Symbol".

 

Is there a measure (e.g. with RELATED() function) that can solve my problem or do I need to adjust my relationships/tables?

 

Many Thanks, Fabian

 

Price Latest = 
CALCULATE(
    AVERAGE(Fact_UnderlyingDaily[Close]), 
    Fact_UnderlyingDaily[Date] = MAX(Fact_UnderlyingDaily[Date])
)

 

My data model setup:

Screenshot 2023-04-01 120557.jpg

1 ACCEPTED SOLUTION
MohammadLoran25
Super User
Super User

Hi @fab_incher ,

Not sure, but Based on your explanation, I think you need the measure below:

 

Measure =
CALCULATE (
    [Price Latest],
    CROSSFILTER ( Fact_OptionTransaction[Symbol], Dim_Symbols[Symbol], BOTH )
)

 

If this answer solves your problem, give it a thumbs up and accept it as a solution so the others would find what they need easier.

 

Regards,

Loran

View solution in original post

2 REPLIES 2
EN_
New Member

Hi,

I have the same scenario, but my first table Fact_underlying_daily has symbols, for which there is no data into the second table. When I use the already provided solution, I only can calculate the values which are in the second fact table. Is there any possibility to have all the values from the first fact table and the additional fields from the second table. Currently when the calculation is correct, the field from the second fact table is visualized many times.

 

EN__0-1685364363807.png

 

MohammadLoran25
Super User
Super User

Hi @fab_incher ,

Not sure, but Based on your explanation, I think you need the measure below:

 

Measure =
CALCULATE (
    [Price Latest],
    CROSSFILTER ( Fact_OptionTransaction[Symbol], Dim_Symbols[Symbol], BOTH )
)

 

If this answer solves your problem, give it a thumbs up and accept it as a solution so the others would find what they need easier.

 

Regards,

Loran

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

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.

Top Solution Authors