topic Re: How to handle this requirement? in DAX Commands and Tips

How to handle this requirement?

Bosmeneer — Fri, 20 Jan 2023 14:10:07 GMT

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

Re: How to handle this requirement?

tamerj1 — Fri, 20 Jan 2023 14:53:26 GMT

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 )

Re: How to handle this requirement?

Bosmeneer — Mon, 23 Jan 2023 15:37:48 GMT

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