Parent-Child Hierarchy - Error thrown by DAX Measure on Matrix Visual

[Anonymous]

Presently I am working on a report which consists of Parent-Child hierarchies to represent our sales organization structure. Rather than reinventing the wheel, I have followed the method suggested in the following web page.

https://www.daxpatterns.com/parent-child-hierarchies/

But the following measure is throwing an error.

TargetValue = 
IF (
    [BrowseDepth] > [MaxNodeDepth] + 1,
    BLANK (),
    IF (
        [BrowseDepth] = [MaxNodeDepth] + 1,
        IF (
            AND (
                VALUES ( GeoNodes1920[IsLeaf] ) = FALSE,
                SUM ( 'Targets 2019-2020'[Value] ) <> 0
            ),
            SUM ( 'Targets 2019-2020'[Value] ),
            BLANK ()
        ),
        SUM ( 'Targets 2019-2020'[Value] )
    )
)

The error message is given below... The point to note here is that I am using a Matrix visual in Power BI and the measure shows the results correctly at the first two levels of the hierarchy. The matrix visual is throwing an error when I drill down to the 3rd level using the "Expand all down one level in the hierarchy" button of the Matrix visual.

Couldn't load the data for this visual

MdxScript(Model) (12,17) Calculation error in measure 'Targets 2019-2020'[TargetValue]: A table of multiple values was supplied where a single value was expected.

Unable to figure out what is causing this error when I drill down to the 3rd level.

[TomMartens]

Hey,

I assume that this line is a causing the error:

...
VALUES ( GeoNodes1920[IsLeaf] ) = FALSE
...

as VALUES(...) basically returns a single column table, that in case there is just one row can be compared to scalar value like "FALSE".

I think you have to prepare for situations where VALUES(...) returns a table with more than one row.

Hopefully this provides some help.

Regards,

Tom

[Anonymous]

@TomMartens 

Can you take a look? I just saw that you are online now.

[TomMartens · Accepted solution]

Hey @Anonymous ,

on a 2nd look I would write the measure like so:

TargetValue = 
IF (
    [BrowseDepth] > [MaxNodeDepth] + 1,
    BLANK (),
    IF (
        [BrowseDepth] = [MaxNodeDepth] + 1,
		CALCULATE(
			SUM ( 'Targets 2019-2020'[Value] )
			,FILTER(
				VALUES ( GeoNodes1920[IsLeaf] )
				,GeoNodes1920[IsLeaf] = FALSE
			)
		)
        ,SUM ( 'Targets 2019-2020'[Value] )
    )
)

This is not well thought as I'm writing this from my mobile, please excuse if this does not work, consider it just an idea 😉

Regards,

Tom

[Anonymous]

I just tried it. It works. 

[Anonymous]

Thanks a lot.