topic Nested SUMMARIZE: how to access column added to inner SUMMARIZE in DAX Commands and Tips

Nested SUMMARIZE: how to access column added to inner SUMMARIZE

Anonymous — Mon, 01 Aug 2022 12:22:31 GMT

I cannot seem to get nested SUMMARIZE working.

I have this measure below, which is working correctly. It give me a flag called _Missing, at (Org, Month) level, which indicates if any sales data is missing for that month.

MyMeasure = SUMX( CALCULATETABLE( ADDCOLUMNS( CROSSJOIN(VALUES('Organisation'[OrgCode]), VALUES('DimDate'[YYYYMM])) ,"_Missing", IF(ISBLANK([Sales]), 1, 0) ,"_OrdQty", [OrdQty] ,"_OrdValue", [OrdQty] ) ,ALL('DimDate') ) ,[_Missing] )

Now what I want to do is get the sum of that _Missing flag at Org level. My intension is for my measure to only returns a value for those orgs that have no missing months i.e. have _Missing = 0 at (Org) level.

I tried this measure below:

MyMeasure = SUMX( SUMMARIZE( CALCULATETABLE( ADDCOLUMNS( CROSSJOIN(VALUES('Organisation'[OrgCode]), VALUES('DimDate'[YYYYMM])) ,"_Missing", IF(ISBLANK([Sales]), 1, 0) ,"_OrdQty", [OrdQty] ,"_OrdValue", [OrdQty] ) ,ALL('DimDate') ) ,'Organisation'[OrgCode] ,"_MissingOrgLevel", SUM([_Missing]) ) ,[_MissingOrgLevel] )

but it generates this error:

'_MissingOrgLevel' cannot be found or may not be used in this expression.

Any ideas how to achieve this?

Re: Nested SUMMARIZE: how to access column added to inner SUMMARIZE

johnt75 — Mon, 01 Aug 2022 12:55:44 GMT

try

MyMeasure = SUMX ( ADDCOLUMNS ( SUMMARIZE ( CALCULATETABLE ( ADDCOLUMNS ( CROSSJOIN ( VALUES ( 'Organisation'[OrgCode] ), VALUES ( 'DimDate'[YYYYMM] ) ), "_Missing", IF ( ISBLANK ( [Sales] ), 1, 0 ), "_OrdQty", [OrdQty], "_OrdValue", [OrdQty] ), ALL ( 'DimDate' ) ), 'Organisation'[OrgCode] ), "_MissingOrgLevel", SUM ( [_Missing] ) ), [_MissingOrgLevel] )

Re: Nested SUMMARIZE: how to access column added to inner SUMMARIZE

Anonymous — Mon, 01 Aug 2022 14:34:03 GMT

johnt75 thanks for the suggerstion, but unfortunately it gives a similar error, this time for the calculated column [_Missing]:

Column '_Missing' cannot be found or may not be used in this expression.

Re: Nested SUMMARIZE: how to access column added to inner SUMMARIZE

johnt75 — Mon, 01 Aug 2022 15:56:00 GMT

Storing the table in a variable may help

MyMeasure = VAR generatedTable = CALCULATETABLE ( ADDCOLUMNS ( CROSSJOIN ( VALUES ( 'Organisation'[OrgCode] ), VALUES ( 'DimDate'[YYYYMM] ) ), "_Missing", IF ( ISBLANK ( [Sales] ), 1, 0 ), "_OrdQty", [OrdQty], "_OrdValue", [OrdQty] ), ALL ( 'DimDate' ) ) RETURN SUMX ( ADDCOLUMNS ( SUMMARIZE ( generatedTable, 'Organisation'[OrgCode] ), "_MissingOrgLevel", SUMX ( generatedTable, [_Missing] ) ), [_MissingOrgLevel] )

BTW, both _OrdQty and _OrdValue use the same measure

Re: Nested SUMMARIZE: how to access column added to inner SUMMARIZE

Anonymous — Mon, 01 Aug 2022 15:57:22 GMT

I have cracked it. I need to create a second measure:

MissingMonths = SUMX( CALCULATETABLE ( ADDCOLUMNS ( CROSSJOIN ( VALUES ( 'Org'[Code] ), VALUES ( 'DimDate'[YYYYMM])), "_Missing", IF ( ISBLANK ( [Sales] ), 1, 0 ) ), ALL ( 'DimDate' ), ) ,[_Missing] )

and then for the Org level, the measure is:

MyMeasure = SUMX( VALUES ( 'Org'[Code] ) , IF([MissingMonths] = 0, [Sales]) )

This returns the total Sales for only those Orgs that do not have any missing months.

Re: Nested SUMMARIZE: how to access column added to inner SUMMARIZE

Anonymous — Tue, 02 Aug 2022 07:43:01 GMT

Thanks , that does allow me to access the [_MissingOrgLevel] column, which is what I was after.

But something is not working correctly. The line

"_MissingOrgLevel", SUMX ( generatedTable, [_Missing] )

does not seem to honour the OrgCode; the line seems to always give the sum of [_Missing] for the entire generatedTable, for every row of the SUMMARIZE.

For example, when I select just one OrgCode which has missing months, then the measure returns 1 as expected. And when I select just onw OrgCode which had zero missing months, then the measure returns 0 as expected.

But when I select both of those two OrgCodes, I want the measure to return 1; but it returns 2.

And if I select 9 OrgCodes that have zero missing months plus one OrgCode that does have missing months, it returns 10.

johnt75

Re: Nested SUMMARIZE: how to access column added to inner SUMMARIZE

johnt75 — Tue, 02 Aug 2022 07:59:55 GMT

change it to

"_MissingOrgLevel", CALCULATE( SUMX ( generatedTable, [_Missing] ) )

that will force it to pick up the org during context transition

Re: Nested SUMMARIZE: how to access column added to inner SUMMARIZE

Anonymous — Tue, 02 Aug 2022 08:31:54 GMT

johnt75 , wrapping it in a CALCULATE did not work. I tried this in the second half of the measure

RETURN CONCATENATEX( SUMMARIZE ( generatedTable, 'Organisation'[OrgCode] ), 'Organisation'[OrgCode] & "(" & CALCULATE(SUMX(generatedTable, [_Missing])) & ")", "," )

and the results when I select three Orgs (0702, 0703, 0140) are below. Notice how the Total row shows all three Orgs with the same SUM of 26 for _Missing.

I have got it working by using a FILTER on the table variable like this:

RETURN CONCATENATEX( SUMMARIZE ( generatedTable, 'Organisation'[OrgCode] ), VAR vOrgCode = 'Organisation'[OrgCode] RETURN 'Organisation'[OrgCode] & "(" & SUMX(FILTER(generatedTable, 'Organisation'[OrgCode] = vOrgCode), [_Missing]) & ")", "," )

which gives these correct results:

Thanks very much for your help, johnt75 .