SUMX of dimension, exclude fact rows with no related marketvalue

[Anonymous]

Hi all,

I'm performing a sumx over a dimension table.

For each iteration in the SUMX I need to validate if the SUM of the marketvalue_exporuse in for that Secitity_ID, the Fact_MeasuresESG is not blank or zero. 

It seems simple, but my only sulution for now is to enable a crossfilter between Dim_Security and FactMeasureseESG, in that way the marketvalue can filter the empty CarbonEmissions_TperUSD.

This doesnt feel performant or logical, can anyone guide me in the right direction?

[Anonymous]

Hi  @Anonymous ,

You can replace the inner part with Var, such as the following form:

Measure =
var _CATEGORY_VALUES=
VALUES('Transformatie Dim_Security'[Dim_Security_ID])
VAR _SumCarbonEmissions_TperUSD=
CALCULATE(SUM('Transfomate Fact_MeasureESG'[CarbonEmissions_TperUSD]))
VAR _SumMarketValue_ReportingCurrency=
CALCULATE(
    SUM('Transformate Fact_MarketValue_Exposure'[MarketValue_ReportingCurrency]),
    'Transfomate Fact_MeasureESG'[CarbonEmissions_TperUSD]<>BLANK() || 'Transfomate Fact_MeasureESG'[CarbonEmissions_TperUSD] >0)
return
CALCULATE(
    DIVIDE(
        SUMX(
            _CATEGORY_VALUES,
            _SumCarbonEmissions_TperUSD * _SumMarketValue_ReportingCurrency),
         SUMX(
             _CATEGORY_VALUES,
             _SumMarketValue_ReportingCurrency)),
             CROSSFILTER('Transformatie Dim_Security'[Dim_Security_ID],'Transfomate Fact_MeasureESG'[Dim_Security_ID],Both))  

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Russell-PBI]

Hi @Anonymous, can you show us the DAX for how you're solving it using a crossfilter as it might help us to better understand what you're trying to achieve?

[Anonymous]

Here's my DAX, it is the crossfilter which seems inefficient. Also filtering twice in seperate SUMX's seems too much. 

[amitchandak]

@Anonymous , In this case, seme like a better solution to have bi-directional join.

Or have a new column, which populate if there is null/0  marketvalue_exporuse flag