Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a 'Customer' table with customer number and vehicle number as below. Here 'Lookup Logic' column is the new column that I want to apply logic on 'Customer' table that will have lookup on another table called 'Status'.
Customer | Vehicle | Lookup Logic |
1234 | ABCBE | Owner |
9876 | ASFGT | Driver |
The customer 1234 has totally 4vechicles in the Status table as seen below. So when we lookup for this customer, we need to search for only those vechicles that are not in the Customer table. For customer 1234, we will then need to lookup for the vechicle numbers TGGEB, DSKFS, SDSDN. Once we hit these 3 records from Status table we will lookup for 'Status' column. If the status that we get for 3 records is mix of 'Owner' and 'Driver' we will need to update the result as 'Owner'(the case for customer 1234). If the status that we get for 3 records is all 'Driver' we will need to update the result as 'Driver'(the case for customer 9876).
Customer | Vehicle | Status |
1234 | ABCBE | Driver |
1234 | TGGEB | Owner |
1234 | DSKFS | Driver |
1234 | SDSDN | Owner |
9876 | ASFGT | Driver |
9876 | FGYUQ | Driver |
9876 | IUIEW | Driver |
I have failed with Lookupvalue dax here as we the result of lookup returns multiple values and we will need to check all those returning records. It would be of great help if someone could assist with the dax logic.
//keshav.
Solved! Go to Solution.
Hi @Anonymous
Try this for your calc column (no relationships between tables):
NewColumn = VAR StatList_ = CALCULATETABLE ( DISTINCT ( Status[Status] ), FILTER ( Status, Status[Customer] = Customer[Customer] && Status[Vehicle] <> Customer[Vehicle] ) ) RETURN IF ( "Owner" IN StatList_, "Owner", "Driver" )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi @Anonymous ,
You could use merge queries to get a new table.
You can treat "Owner" as 1 and treat "Driver" as 0. If the sum>0,then the result is "Owner".
Here is the m query you can refer to.
let Source = Table.NestedJoin(Status, {"Vehicle", "Customer"}, Custom, {"Vehicle", "Customer"}, "Custom", JoinKind.LeftAnti), #"Removed Columns" = Table.RemoveColumns(Source,{"Vehicle", "Custom"}), #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","Owner","1",Replacer.ReplaceText,{"Status"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Driver","0",Replacer.ReplaceText,{"Status"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Status", Int64.Type}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Customer", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Customer", type text}}, "en-US")[Customer]), "Customer", "Status", List.Sum), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column", {}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Value]>0 then "Owner" else "Driver"), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Value"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Attribute", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Attribute"}, Custom, {"Customer"}, "Custom.1", JoinKind.LeftOuter), #"Expanded Custom.1" = Table.ExpandTableColumn(#"Merged Queries", "Custom.1", {"Vehicle"}, {"Custom.1.Vehicle"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom.1",{"Attribute", "Custom.1.Vehicle", "Custom"}) in #"Reordered Columns"
Hi @Anonymous
Try this for your calc column (no relationships between tables):
NewColumn = VAR StatList_ = CALCULATETABLE ( DISTINCT ( Status[Status] ), FILTER ( Status, Status[Customer] = Customer[Customer] && Status[Vehicle] <> Customer[Vehicle] ) ) RETURN IF ( "Owner" IN StatList_, "Owner", "Driver" )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |