Forum Discussion

PowerBeeEye's avatar
PowerBeeEye
Microsoft Employee
6 years ago
Solved

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 Con...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    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-1gyha81hBg848d7QkKpMBSLyFWmUxf-RBcLsYkcjbDA?e=WtPhHJ

     

    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's avatar
HotChilli
Community Champion
6 years ago

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.