Forum Discussion

Hanuma_Srikiran's avatar
Hanuma_Srikiran
Frequent Visitor
8 years ago
Solved

Comparing Two columns with Output (Parent and Child relation)

Hi , I have a source file like below Child Parent Parrot Pet LoveBirds Pet Eagle Adventures Bat Adventures Lion Wild Tiger Wild Cow Domestic Goat Domestic Wha...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Hanuma_Srikiran

     

    Try this Calculated Table.

     

    New Table =
VAR AllParent =
    SELECTCOLUMNS ( TableName, "AllParents", TableName[Parent] )
VAR Firstparent =
    ADDCOLUMNS (
        EXCEPT ( ALL ( TableName[Child] ), ALL ( TableName[Parent] ) ),
        "FirstParent", CALCULATE ( VALUES ( TableName[Parent] ) )
    )
VAR secondparent =
    ADDCOLUMNS (
        SELECTCOLUMNS (
            Firstparent,
            "LastChild", [Child],
            "FirstParent", [FirstParent]
        ),
        "SecondParent", LOOKUPVALUE ( TableName[Parent], TableName[Child], [FirstParent] )
    )
VAR thirdparent =
    ADDCOLUMNS (
        SUMMARIZE ( secondparent, [LastChild], [FirstParent], [SecondParent] ),
        "ThirdParent", LOOKUPVALUE ( TableName[Parent], TableName[Child], [SecondParent] )
    )
VAR Fourthparent =
    ADDCOLUMNS (
        SUMMARIZE (
            thirdparent,
            [LastChild],
            [FirstParent],
            [SecondParent],
            [ThirdParent]
        ),
        "FourthParent", LOOKUPVALUE ( TableName[Parent], TableName[Child], [ThirdParent] )
    )
RETURN
    FILTER (
        UNION (
            SUMMARIZE ( Fourthparent, [LastChild], [FirstParent] ),
            SUMMARIZE ( Fourthparent, [LastChild], [SecondParent] ),
            SUMMARIZE ( Fourthparent, [LastChild], [ThirdParent] ),
            SUMMARIZE ( Fourthparent, [LastChild], [FourthParent] )
        ),
        [FirstParent] <> BLANK ()
    )

     

     

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi Hanuma_Srikiran

 

Try this Calculated Table.

 

New Table =
VAR AllParent =
    SELECTCOLUMNS ( TableName, "AllParents", TableName[Parent] )
VAR Firstparent =
    ADDCOLUMNS (
        EXCEPT ( ALL ( TableName[Child] ), ALL ( TableName[Parent] ) ),
        "FirstParent", CALCULATE ( VALUES ( TableName[Parent] ) )
    )
VAR secondparent =
    ADDCOLUMNS (
        SELECTCOLUMNS (
            Firstparent,
            "LastChild", [Child],
            "FirstParent", [FirstParent]
        ),
        "SecondParent", LOOKUPVALUE ( TableName[Parent], TableName[Child], [FirstParent] )
    )
VAR thirdparent =
    ADDCOLUMNS (
        SUMMARIZE ( secondparent, [LastChild], [FirstParent], [SecondParent] ),
        "ThirdParent", LOOKUPVALUE ( TableName[Parent], TableName[Child], [SecondParent] )
    )
VAR Fourthparent =
    ADDCOLUMNS (
        SUMMARIZE (
            thirdparent,
            [LastChild],
            [FirstParent],
            [SecondParent],
            [ThirdParent]
        ),
        "FourthParent", LOOKUPVALUE ( TableName[Parent], TableName[Child], [ThirdParent] )
    )
RETURN
    FILTER (
        UNION (
            SUMMARIZE ( Fourthparent, [LastChild], [FirstParent] ),
            SUMMARIZE ( Fourthparent, [LastChild], [SecondParent] ),
            SUMMARIZE ( Fourthparent, [LastChild], [ThirdParent] ),
            SUMMARIZE ( Fourthparent, [LastChild], [FourthParent] )
        ),
        [FirstParent] <> BLANK ()
    )

 

 

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hanuma_Srikiran

       

      If you want parents in separate columns then you can try this calculated table

       

       

      4 Parents =
VAR AllParent =
    SELECTCOLUMNS ( TableName, "AllParents", TableName[Parent] )
VAR LC_FP =
    ADDCOLUMNS (
        EXCEPT ( ALL ( TableName[Child] ), ALL ( TableName[Parent] ) ),
        "FirstParent", CALCULATE ( VALUES ( TableName[Parent] ) )
    )
VAR secondparent =
    ADDCOLUMNS (
        SELECTCOLUMNS ( LC_FP, "LastChild", [Child], "FirstParent", [FirstParent] ),
        "SecondParent", LOOKUPVALUE ( TableName[Parent], TableName[Child], [FirstParent] )
    )
VAR thirdparent =
    ADDCOLUMNS (
        SUMMARIZE ( secondparent, [LastChild], [FirstParent], [SecondParent] ),
        "ThirdParent", LOOKUPVALUE ( TableName[Parent], TableName[Child], [SecondParent] )
    )
VAR Fourthparent =
    ADDCOLUMNS (
        SUMMARIZE (
            thirdparent,
            [LastChild],
            [FirstParent],
            [SecondParent],
            [ThirdParent]
        ),
        "FourthParent", LOOKUPVALUE ( TableName[Parent], TableName[Child], [ThirdParent] )
    )
RETURN
    Fourthparent
  • Hanuma_Srikiran's avatar
    Hanuma_Srikiran
    Frequent Visitor
    8 years ago

    Thanks Zubair_Muhammad  Great Work done.

     

    In this scenario, there are only 4 parents but it may be more than 4 parents in some cases. It varies on animal type to type. At that time, the following DAX won't work.

    Can you help in this ?

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hanuma_Srikiran

       

      I think it would be easy.

       

      You would have to add 5th parent and so on in the same pattern as 3rd and 4th parent ....inside the "VAR"

       

      And then add it to the Union Query.

       

      If it doesn't work, please copy paste your full data here (or provide the file).... I will try to fix it

       

       

      • Hanuma_Srikiran's avatar
        Hanuma_Srikiran
        Frequent Visitor
        8 years ago

        Yeah, it's easy by hard coding the dax.

        But I want it to be dynamic with respect to the number of Parents, without hardcoding the DAX.