topic Re: lookup same table for referenced field values in DAX Commands and Tips

lookup same table for referenced field values

aherabit — Wed, 13 Dec 2023 18:00:13 GMT

Hi Folks,

I have this DB

issue key	type	description	epic link
s-2	defect	defect description s-2	s-20
s-3	requirement	requirement description s-3	s-15
s-4	requirement	requirement description s-4	s-15
s-15	epic	epic description s-15
s-18	defect	description s-18	s-15
s-20	epic	description s-20
s-101	requirement	description s-101	s-20
s-205	defect	description s-201	s-15

Need to be able to find all 'defect' and display the 'requirement' that is linked to 'epic'.

defect	epic	epic description	requirements
s-2	s-20	description s-20	s-101
s-18	s-15	epic description s-15	s-3, s-4
s-205	s-15	epic description s-15	s-101

In other other words, for every 'defect' find the 'requirement' that has the same 'epic link'.

Appreciate any pointers.

Re: lookup same table for referenced field values

smpa01 — Wed, 13 Dec 2023 19:45:34 GMT

aherabit does this work?

Measure = VAR one = CALCULATE ( MAX ( t1[epic link] ), FILTER ( ALL ( t1 ), t1[issue key] = MAX ( t1[issue key] ) ) ) RETURN CONCATENATEX ( FILTER ( ALL ( t1 ), t1[type] = "requirement" && t1[epic link] = one ), t1[issue key], ",", t1[issue key] )

Re: lookup same table for referenced field values

aherabit — Wed, 13 Dec 2023 20:52:23 GMT

thanks...but its getting stuck on the Filter arguments. On the first t1[type]. (parameter is not the correct type)

RETURN CONCATENATEX ( FILTER ( ALL ( t1 ), t1[type] = "requirement" && t1[epic link] = one ), t1[issue key], ",", t1[issue key] )

Re: lookup same table for referenced field values

Tom_Y — Wed, 13 Dec 2023 21:38:05 GMT

If I were you, I will duplicate the same table and called it "Requirement". In the Requirement Table, filter row and only show rows with requirements, and remove duplicate, so that table will be something like

Requirement/ epic link/

s3 / s15

s4 / s15

s101 / s20

and then you just build relationship and pot everything into one "Matrix" visual, and filter Defect only.

By the way, your example is shoiwng unclear logic, defect s205 is referring to epic link s15, but epic link s15 is referring to requirement s3,s4 and s101.

(Anyway, the easiest method seesm to be duplicate the table, and clean it up, remove duplicate, remove irrelevant, and make it a Dimension table for you to feed data back into the fact table)

Re: lookup same table for referenced field values

aherabit — Thu, 14 Dec 2023 21:10:06 GMT

Thank you, Tom.

This is the simplest approach and I used a 'referenced' duplicate function to get away from creating and entire table.