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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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



Super User
Super User

Hi @Bosmeneer 

please try the following measure 

VAR TotalMarketValue =
SUM ( Fact_Marketvalue[Marketvalue] )
VAR RequiredSecurityIDs =
VALUES ( Fact_Ratings[Dim_Security_ID] ),
Fact_Ratings[Carbonemission] > 5
VAR RequiredCarbonemission =
SUM ( Fact_Rating[Carbonemission] ),
Fact_Ratings[Carbonemission] > 5
VAR RequiredMarketValue =
SUM ( Fact_Marketvalue[Marketvalue] ),
Fact_Marketvalue[Dim_Security_ID] IN RequiredSecurityIDs
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



Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Power BI Carousel June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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