Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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

View solution in original post

2 REPLIES 2
v-eachen-msft
Community Support
Community Support

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"

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
AlB
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.