Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

8 REPLIES 8
Anonymous
Not applicable

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,

 

 

 

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.