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

ChildParent
ParrotPet
LoveBirdsPet
EagleAdventures
BatAdventures
LionWild
TigerWild
CowDomestic
GoatDomestic
WhaleWater
PetBird
AdventuresBird
WildLand
DomesticLand
LandMammal
WaterMammal
MammalsLiving
BirdLiving

 

 

I want Output like below 

 

ParrotPet
ParrotBird
ParrotLiving
LoveBirdsPet
LoveBirdsBird
LoveBirdsLiving
EagleAdventures
EagleBird
EagleLiving
BatAdventures
BatBird
BatLiving
LionWild
LionLand
LionMammal
LionLiving
TigerWild
TigerLand
TigerMammal
TigerLiving

 

 and so on ...

 

 

Please anyone can help ??

Thanks in Advance

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

     

     

8 Replies

  • 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

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    hello every one i have a similar issue:

    i have a table witch contains all services 

     

    Service1

    Service2

    Service3

    Service4

    Service5

    Service6

    Service7

    and another table witch contains services with features and i want to pie chart and show whitch services are not being used (plot their name)

    thank you,