Conditional column based on correlation of data from two columns

[PowerBeeEye]

I have a table like the one below, where one forest may contain one or more domains. Some of the domains may not have the same contiguous name as the forest (see 5th row)

Forest	Domain
Contoso.com	Contoso.com
Contoso.com	Corp.Contoso.com
Fabrikam.net	Fabrikam.net
Fabrikam.net	Child.Fabrikam.net
Fabrikam.net	Fabrikam.com
Fabrikam.net	Corp.Fabrikam.com
WingtipToys.ca	WingtipToys.ca

I would like to add two columns, ForestType and DomainType:

Forest	Domain	ForestType	DomainType
Contoso.com	Contoso.com	Multi-domain	Forest-root
Contoso.com	Corp.Contoso.com	Multi-domain	Child
Fabrikam.net	Fabrikam.net	Multi-domain	Forest-root
Fabrikam.net	Child.Fabrikam.net	Multi-domain	Child
Fabrikam.net	Fabrikam.com	Multi-domain	Tree-root
Fabrikam.net	Corp.Fabrikam.com	Multi-domain	Child
WingtipToys.ca	WingtipToys.ca	Single-domain	Forest-root

Logic:

• ForestType
  • If Forest = Domain, and Forest is unique, ForestType = Single-domain
  • If Forest = Domain, and Forest is not unique, ForestType = Multi-domain
  • If Forest != Domain, ForestType = Multi-domain
• DomainType
  • If Domain = Forest, DomainType = Forest-root
  • If Domain != Forest, and there is no other Domain value that is a substring, DomainType = Tree-root
  • If Domain != Forest, and there is one or more Domain value that is a substring, DomainType = Child

Thanks a ton!

[v-easonf-msft · Accepted solution]

Hi , @PowerBeeEye 

According to you description, you can follow these steps:

1) Constructing three auxiliary columns

forest equal domain = 'Table'[Forest]='Table'[Domain]

forest unique = CALCULATE(COUNTA('Table'[Forest]),ALLEXCEPT('Table','Table'[Forest]))

IS Substring = FIND('Table'[Forest],'Table'[Domain],,-1)

 2)Create calculated columns based on conditions

Forest Type = SWITCH( TRUE(),
'Table'[forest equal domain]=TRUE()&&'Table'[forest unique]= 1,"Single-domain",
'Table'[forest equal domain]=TRUE()&& 'Table'[forest unique] <> 1,"Multi-domain",
'Table'[forest equal domain]=FALSE(),"Multi-domain")

Domain Type = SWITCH( TRUE(),
'Table'[forest equal domain]=TRUE(),"Forest-root",
'Table'[forest equal domain]=FALSE()&& 'Table'[IS Substring]=-1,"Tree-root",
'Table'[forest equal domain]=FALSE()&& 'Table'[IS Substring]>0,"Child")

Here is a sample I  made :

URL:https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/Ec-1gyha81hBg848d7QkK...

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

In Power Query.  For ForestType, you could do a 'group by' as follows

Then expand the 'All' column.

Add a Custom Column.

if [Forest] = [All.Domain] and [Count] = 1 then "Single-domain" else "Multi-Domain"

I've shortened the logic you provided but feel free to edit your own version.

I might have a go at part 2 if you explain further about the substring logic.