lookup same table for referenced field values

[aherabit]

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.

[Tom_Y · Accepted solution]

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)

[aherabit]

Thank you, Tom.

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

[smpa01]

@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]
    )

[aherabit]

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]
    )