topic Re: SUMX of dimension, exclude fact rows with no related marketvalue in DAX Commands and Tips

SUMX of dimension, exclude fact rows with no related marketvalue

Anonymous — Thu, 24 Feb 2022 12:51:08 GMT

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?

Re: SUMX of dimension, exclude fact rows with no related marketvalue

amitchandak — Thu, 24 Feb 2022 14:56:46 GMT

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

Re: SUMX of dimension, exclude fact rows with no related marketvalue

Russell-PBI — Thu, 24 Feb 2022 14:59:50 GMT

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?

Re: SUMX of dimension, exclude fact rows with no related marketvalue

Anonymous — Thu, 24 Feb 2022 15:06:40 GMT

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

Re: SUMX of dimension, exclude fact rows with no related marketvalue

Anonymous — Wed, 02 Mar 2022 06:27:14 GMT

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.