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

How to handle this requirement?

Hi all,

I've been struggling with a scenario for a while which I want to solve properly. Below I've created a model which contains the relevant tables from a more complex data model. 

The requirement
Calculate the following
Divide this
SUM(Fact_Marketvalue.Marketvalue) * SUM(Fact_Rating.Carbonemission)
where the Fact_Ratings.Carbonemission > 5) 
by this
SUM(Fact_Marketvalue.Marketvalue) > 5 )

The result should by sliceable by Category, Customer, Security and more Dimension tables in my model.
Right now I created many-many relationships between Fact_MarketValue and Fact_Ratings on the Security ID, but I want to avoid this relationship.

What would be the best thing to do? Create a view in SQL which holds all information needed, if so, I want to keep it as lean as possible? or should I create a bridge table, if so, with what information? Or maybe I am missing some DAX magic..

 

Capture.PNG

2 REPLIES 2
tamerj1
Super User
Super User

Hi @Bosmeneer 

please try the following measure 

=
VAR TotalMarketValue =
SUM ( Fact_Marketvalue[Marketvalue] )
VAR RequiredSecurityIDs =
CALCULATETABLE (
VALUES ( Fact_Ratings[Dim_Security_ID] ),
Fact_Ratings[Carbonemission] > 5
)
VAR RequiredCarbonemission =
CALCULATE (
SUM ( Fact_Rating[Carbonemission] ),
Fact_Ratings[Carbonemission] > 5
)
VAR RequiredMarketValue =
CALCULATE (
SUM ( Fact_Marketvalue[Marketvalue] ),
Fact_Marketvalue[Dim_Security_ID] IN RequiredSecurityIDs
)
RETURN
DIVIDE ( TotalMarketValue * RequiredCarbonemission, RequiredMarketValue )

Thanks @tamerj1 
I dont think this will work because I need to calculate the marketvalue * carbonemission row by row. Taking it as a total summarized value and then multiplying it will give me an inccorect value.

Right now I am doing this which gives me the correct result, but I want to get rid of the userelationship which enables a many-many relationship

 

eNJwg.png

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.