cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sreenathv
Super User
Super User

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

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.

 

1 ACCEPTED SOLUTION

Hey @sreenathv ,

 

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

5 REPLIES 5
TomMartens
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
sreenathv
Super User
Super User

@TomMartens 

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

 

Hey @sreenathv ,

 

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

I just tried it. It works. 

Thanks a lot.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.