Forum Discussion
Conditional column based on correlation of data from two columns
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!
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 :
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.
2 Replies
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.
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 :
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.
