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

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

Accepted Solutions
Super User
Super User

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

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

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
sreenathv Member
Member

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

@TomMartens 

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

 

Super User
Super User

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

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

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

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

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

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

sreenathv Member
Member

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

Thanks a lot.

Highlighted
sreenathv Member
Member

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

I just tried it. It works. 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 142 members 1,886 guests
Please welcome our newest community members: