cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hanuma_Srikiran
Frequent Visitor

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

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

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

 

 

Regards
Zubair

Please try my custom visuals


View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

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

 

 

Regards
Zubair

Please try my custom visuals


View solution in original post

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 ?

@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

 

 

Regards
Zubair

Please try my custom visuals


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.

Hi @Hanuma_Srikiran

 

I believe this requires a recursive operation which is not supported in DAX....

 

But there might be a solution in Power Query....

Regards
Zubair

Please try my custom visuals


@Hanuma_Srikiran

 

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

 

4parents.png

 

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
Regards
Zubair

Please try my custom visuals


Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors