cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to display relevant data of Table "Bar" using a reference in Table "Foo"'s data?

Hello,

 

I have the following problem:

The table Foo has a serial number (serial_number) that corresponds to a serial number in the table Bar. This is not an unique value as new entries on both Foo and Bar are entered that may contain an existing serial number (and that "replaces" a previous entry) are supposed to supersede previous entries (and thus cardinality is many to many😞

image.png

 


What is needed is to retrieve the Bar[id] and Bar[description] of an item two levels above the Bar-row that is referenced in the serial number. In practice this means:

If Foo row's serial number is

waffaru_2-1638365625887.png

Then the corresponding Bar row is:

waffaru_4-1638365725270.png

 

And we want to get the id and description of this Bar row (because this item is two levels above):

waffaru_5-1638365822558.png


I have the current Dax expression which works in one to one cardinality. This gets the grandparent_id (Another nearly similar exists for the grandparent description. Both have been tested):

Grandparent_id = 
IF(
    HASONEVALUE(Bar[id]),
    LOOKUPVALUE(
        Bar[id],
        Bar[id],
        PATHITEMREVERSE(
            Bar[Path],
            3, INTEGER
        ), BLANK()
    )
)


In this case, adding Grandparent_id to the Foo table will display the related Bar table's grandparent's id successfully. But because the cardinality is Many to Many (and needs to be Many to Many, due to how the related database was designed), this doesn't seem to work. the LOOKUPVALUE in this case just returns blank, despite a relation existing between Foo and Bar.

How should I go around this problem in order to be able to display the grandparent id and description on the Foo table? Below you can see that if I add said measurements to the Foo table it becomes empty (on the left of it there is another Foo table displayed which doesn't have the measurements in it, and works fine.)

waffaru_6-1638366144482.png


Each entry in the Bar table has a parent_id reference, which allows me to create a PATH using Bar[id] and Bar[parent_id]:

Path = IF(
    HASONEVALUE(Bar[id]),
    PATH(
        Bar[id],
        Bar[parent_id]
    )
)

 

Thanks in advance for the help.

1 ACCEPTED SOLUTION

Hi @Anonymous ,

Here I suggest you to inactive the relationship between two tables. This relationship will cause the result from measure incorrect.

Then you only need to create two measures.

GrandParentID from Bar = 
VAR _ParentID =
    CALCULATE (
        SUM ( Bar[parent_id] ),
        FILTER ( ALL ( Bar ), Bar[serial_number] = MAX ( Foo[serial number] ) )
    )
VAR _GrandParentID =
    CALCULATE (
        SUM ( Bar[parent_id] ),
        FILTER ( ALL ( Bar ), Bar[id] = _ParentID )
    )
RETURN
    _GrandParentID
GrandParent Description from Bar = 
CALCULATE (
    MAX ( Bar[description] ),
    FILTER ( ALL ( Bar ), Bar[id] = [GrandParentID from Bar] )
)

Result is as below.

1.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

You can find the Pbix file I'm using on the following link. All the necessary sample data is there.
https://drive.google.com/file/d/1e7bQc2T8WVZLjGSMNILFqmXUfUwIAWFc/view?usp=sharing

 

 

Hi @Anonymous ,

Here I suggest you to inactive the relationship between two tables. This relationship will cause the result from measure incorrect.

Then you only need to create two measures.

GrandParentID from Bar = 
VAR _ParentID =
    CALCULATE (
        SUM ( Bar[parent_id] ),
        FILTER ( ALL ( Bar ), Bar[serial_number] = MAX ( Foo[serial number] ) )
    )
VAR _GrandParentID =
    CALCULATE (
        SUM ( Bar[parent_id] ),
        FILTER ( ALL ( Bar ), Bar[id] = _ParentID )
    )
RETURN
    _GrandParentID
GrandParent Description from Bar = 
CALCULATE (
    MAX ( Bar[description] ),
    FILTER ( ALL ( Bar ), Bar[id] = [GrandParentID from Bar] )
)

Result is as below.

1.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Oh my god, thank you so much for this! I have no idea yet how the measures work in detail but I will go through them and figure out the steps. For now I'm going to try to apply it to my actual dataset this test dataset is based on, and I'll reply if I bump into any other issues I can't overcome by myself.

 

Thank you once again, I've had many sleepless nights dreading this problem.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!