How to handle this requirement?

[Bosmeneer]

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..

[tamerj1]

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 )

[Bosmeneer]

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