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 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 requir...
"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)

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