Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.