Forum Discussion
IF and Lookup query with mutiple table
I have a table similiar to below one.
I need to create a new calculated column - It should return the value from different columns on below mentioned order.
Forr Example: If no matches found with first column then do lookup with next column, continue till the last column.
1. First "Requester" has to be looked up with "Director" then whatever the value is NA - it has to be lookedup with "Manager" then whatever the valueis NA - it has to be lookedup with "Team Manager" then whatever the value is NA - "Location Manager" has to be looked up with "Director" then whatever the value is NA - "Location Manager" has to be looked up with "Manager" - then whatever the value is NA - "Location Manager" has to be looked up with "Team Manager"
Table1:
| Requestor | Location Manager |
| John | Wic |
| Peter | Wic |
| Kevin | Wic |
| Kim | Wic |
| Khan | Wic |
| Director | Manager | Team Manager |
| John | Peter | Kim |
| Kevin | Khan | |
| Wic |
Hi dhandapani ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) If you want the [Requestor] column of Table 1 to find the corresponding data in the first column of Table 2, output that data and stop searching towards the next two columns, which you can create.
Stop when you find it = var _a= ADDCOLUMNS('Table',"column1",IF('Table'[Requestor] in VALUES('Table (2)'[Director]),'Table'[Requestor],"NA")) var _b=ADDCOLUMNS('Table',"column2",IF('Table'[Requestor] in VALUES('Table (2)'[Manager]),'Table'[Requestor],"NA")) var _c=ADDCOLUMNS('Table',"column3",IF('Table'[Requestor] in VALUES('Table (2)'[Team Manager]),'Table'[Requestor],"NA")) var _rows=COUNTROWS(ALL('Table')) var _dRows =COUNTROWS(FILTER(_a,[column1]="NA")) var _mRows =COUNTROWS(FILTER(_b,[column2]="NA")) var _tRows =COUNTROWS(FILTER(_c,[column3]="NA")) return SWITCH(TRUE(), _rows<>_dRows && 'Table'[Requestor] in VALUES('Table (2)'[Director]),'Table'[Requestor], _rows=_dRows && _rows <> _mRows && 'Table'[Requestor] in VALUES('Table (2)'[Manager]),'Table'[Requestor], _rows=_dRows && _rows = _mRows && _rows <> _tRows && 'Table'[Requestor] in VALUES('Table (2)'[Team Manager]),'Table'[Requestor], "NA")(3)If you want the [Requestor] column of Table 1 to find the corresponding data in the first column of Table 2, but do not stop and continue to search in the next two columns of Table 2, you can create the following.
Check all layers = var a=COUNTROWS(FILTER('Table (2)',[Director]=EARLIER('Table'[Requestor]))) var b=COUNTROWS(FILTER('Table (2)',[Manager]=EARLIER('Table'[Requestor]))) var c=COUNTROWS(FILTER('Table (2)',[Team Manager]=EARLIER('Table'[Requestor]))) return IF(a>0||b>0||c>0,[Requestor],"NA")The Hierarchy column indicates the column in which the row is found in Table 2, for example, 1 indicates that the row is found in the Director column of Table 2.
Hierarchy = var a=COUNTROWS(FILTER('Table (2)',[Director]=EARLIER('Table'[Requestor]))) var b=COUNTROWS(FILTER('Table (2)',[Manager]=EARLIER('Table'[Requestor]))) var c=COUNTROWS(FILTER('Table (2)',[Team Manager]=EARLIER('Table'[Requestor]))) return SWITCH(TRUE(), a>0,"1", a=0 && b>0,"2", a=0 && b=0 &&c>0,"3", "NA")(4) Then the result is as follows.
To find the Location Manager column, simply replace Requester with Location Manager in the created calculation column. You can refer to my pbix file.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi dhandapani ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) If you want the [Requestor] column of Table 1 to find the corresponding data in the first column of Table 2, output that data and stop searching towards the next two columns, which you can create.
Stop when you find it = var _a= ADDCOLUMNS('Table',"column1",IF('Table'[Requestor] in VALUES('Table (2)'[Director]),'Table'[Requestor],"NA")) var _b=ADDCOLUMNS('Table',"column2",IF('Table'[Requestor] in VALUES('Table (2)'[Manager]),'Table'[Requestor],"NA")) var _c=ADDCOLUMNS('Table',"column3",IF('Table'[Requestor] in VALUES('Table (2)'[Team Manager]),'Table'[Requestor],"NA")) var _rows=COUNTROWS(ALL('Table')) var _dRows =COUNTROWS(FILTER(_a,[column1]="NA")) var _mRows =COUNTROWS(FILTER(_b,[column2]="NA")) var _tRows =COUNTROWS(FILTER(_c,[column3]="NA")) return SWITCH(TRUE(), _rows<>_dRows && 'Table'[Requestor] in VALUES('Table (2)'[Director]),'Table'[Requestor], _rows=_dRows && _rows <> _mRows && 'Table'[Requestor] in VALUES('Table (2)'[Manager]),'Table'[Requestor], _rows=_dRows && _rows = _mRows && _rows <> _tRows && 'Table'[Requestor] in VALUES('Table (2)'[Team Manager]),'Table'[Requestor], "NA")(3)If you want the [Requestor] column of Table 1 to find the corresponding data in the first column of Table 2, but do not stop and continue to search in the next two columns of Table 2, you can create the following.
Check all layers = var a=COUNTROWS(FILTER('Table (2)',[Director]=EARLIER('Table'[Requestor]))) var b=COUNTROWS(FILTER('Table (2)',[Manager]=EARLIER('Table'[Requestor]))) var c=COUNTROWS(FILTER('Table (2)',[Team Manager]=EARLIER('Table'[Requestor]))) return IF(a>0||b>0||c>0,[Requestor],"NA")The Hierarchy column indicates the column in which the row is found in Table 2, for example, 1 indicates that the row is found in the Director column of Table 2.
Hierarchy = var a=COUNTROWS(FILTER('Table (2)',[Director]=EARLIER('Table'[Requestor]))) var b=COUNTROWS(FILTER('Table (2)',[Manager]=EARLIER('Table'[Requestor]))) var c=COUNTROWS(FILTER('Table (2)',[Team Manager]=EARLIER('Table'[Requestor]))) return SWITCH(TRUE(), a>0,"1", a=0 && b>0,"2", a=0 && b=0 &&c>0,"3", "NA")(4) Then the result is as follows.
To find the Location Manager column, simply replace Requester with Location Manager in the created calculation column. You can refer to my pbix file.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.