Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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😞
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
Then the corresponding Bar row is:
And we want to get the id and description of this Bar row (because this item is two levels above):
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.)
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.
Solved! Go to 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.
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.
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.
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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |