Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Whale | Water |
Pet | Bird |
Adventures | Bird |
Wild | Land |
Domestic | Land |
Land | Mammal |
Water | Mammal |
Mammals | Living |
Bird | Living |
I want Output like below
Parrot | Pet |
Parrot | Bird |
Parrot | Living |
LoveBirds | Pet |
LoveBirds | Bird |
LoveBirds | Living |
Eagle | Adventures |
Eagle | Bird |
Eagle | Living |
Bat | Adventures |
Bat | Bird |
Bat | Living |
Lion | Wild |
Lion | Land |
Lion | Mammal |
Lion | Living |
Tiger | Wild |
Tiger | Land |
Tiger | Mammal |
Tiger | Living |
and so on ...
Please anyone can help ??
Thanks in Advance
Solved! Go to Solution.
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 () )
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,
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 () )
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 ?
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
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.
I believe this requires a recursive operation which is not supported in DAX....
But there might be a solution in Power Query....
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
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |