Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power BI DAX - Lookup returning multiple rows and logic on multiple rows

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'.

 

CustomerVehicleLookup Logic
1234ABCBEOwner
9876ASFGTDriver

 

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).

 

CustomerVehicleStatus
1234ABCBEDriver
1234TGGEBOwner
1234DSKFSDriver
1234SDSDNOwner
9876ASFGTDriver
9876FGYUQDriver
9876IUIEWDriver

 

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.

Need Help
  • AlB's avatar
    AlB
    6 years ago

    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  Datanaut

2 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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  Datanaut

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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"