Forum Discussion

aherabit's avatar
aherabit
Icon for Helper I rankHelper I
2 years ago
Solved

lookup same table for referenced field values

Hi Folks,

I have this DB

issue keytypedescriptionepic link
s-2defectdefect description s-2s-20
s-3requirement  requirement description s-3s-15
s-4requirementrequirement description s-4s-15
s-15epicepic description s-15 
s-18defectdescription s-18s-15
s-20epicdescription s-20 
s-101requirementdescription s-101s-20
s-205defectdescription s-201s-15

 

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

defectepicepic descriptionrequirements
s-2s-20  description s-20  s-101
s-18s-15 epic description s-15   s-3, s-4
s-205s-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.

"DAX help"
"Need Help"
  • Tom_Y's avatar
    Tom_Y
    2 years ago

    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)

4 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      aherabit
      Icon for Helper I rankHelper I
      2 years ago

      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]
    )
  • Tom_Y's avatar
    Tom_Y
    Icon for Advocate II rankAdvocate II
    2 years ago

    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's avatar
      aherabit
      Icon for Helper I rankHelper I
      2 years ago

      Thank you, Tom.

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