cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Checking for Partial Row Matches in the Same Table

 

Hi Folks,

I have an table called "Client_Shipments" with the following columns: [Client_name], [Send_location], [Receive_location], [Cost], [Error_rate]. I'm trying to check if for the client_name in each row, there's a match in the same table "Client_Shipments", which has the same values for [Send_Location], [Receive_Location], but does not have the same Client_name.

 

Basically, are there other clients who've sent something from the same start and end point? The [Cost] and [Error_rate] values do not need to match, since that's what I want to compare later. I created an example table below, along with the desired output in column [Match_found?].

 

Client_nameSend_locationReceive_locationCostError_rateMatch_found?
Client_ACANJ$10001%true
Client_AAZIL$30010%true
Client_AFLDE$4005%false
Client_BMACO15003%false
Client_BMOAK$5001%false
Client_BAZIL$4005%true
Client_BCANJ$12001%true

 

Any thoughts on how to make that work? Thank you in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Supplier
Solution Supplier

Re: Checking for Partial Row Matches in the Same Table

Hi @ndeshpande 

You could add it as a new column:

 

Match_Found_Col =
VAR cl = [Client_name]
VAR sl = [Send_location]
VAR rl = [Receive_location]
VAR c =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( ClientOrders ),
            FILTER (
                ALL ( ClientOrders ),
                [Send_location] = sl
                    && [Receive_location] = rl
                    && [Client_name] <> cl
            )
        )
    ) + 0
RETURN
    IF ( c > 0, TRUE (), FALSE () )

 


Or as a measure:

 

Match_Found_Mes =
VAR cl =
    FIRSTNONBLANK ( ClientOrders[Client_name], 1 )
VAR sl =
    SELECTEDVALUE ( ClientOrders[Send_location], "123" )
VAR rl =
    SELECTEDVALUE ( ClientOrders[Receive_location], "123" )
VAR c =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( ClientOrders ),
            FILTER (
                ALL ( ClientOrders ),
                [Send_location] = sl
                    && [Receive_location] = rl
                    && [Client_name] <> cl
            )
        )
    ) + 0
RETURN
    IF ( c > 0, TRUE (), FALSE () )

 

 

Cheers


Edit: Ah, sorry @parry2k , I hadn't seen your answer.




Feel free to connect with me:
LinkedIn

View solution in original post

4 REPLIES 4
Highlighted
Super User VII
Super User VII

Re: Checking for Partial Row Matches in the Same Table

@ndeshpande you can try following method

 

Add new column and measure and measure will return true/false

 

Receive and Send Column = 'Table'[Receive_location] & 'Table'[Send_location]


Is Exist Measure = 
VAR __current = VALUES ( 'Table'[Receive and Send Column] )
VAR __client = SELECTEDVALUE ( 'Table'[Client_name] )
VAR __other = CALCULATETABLE ( VALUES ( 'Table'[Receive and Send Column] ), ALL ( 'Table'[Receive and Send Column] ), 'Table'[Client_name] <> __client )
VAR __isExist = CALCULATE ( COUNTROWS ( 'Table' ), INTERSECT ( __other, __current ) ) + 0
RETURN
IF ( __isExist = 0, "False", "True" )

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Solution Supplier
Solution Supplier

Re: Checking for Partial Row Matches in the Same Table

Hi @ndeshpande 

You could add it as a new column:

 

Match_Found_Col =
VAR cl = [Client_name]
VAR sl = [Send_location]
VAR rl = [Receive_location]
VAR c =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( ClientOrders ),
            FILTER (
                ALL ( ClientOrders ),
                [Send_location] = sl
                    && [Receive_location] = rl
                    && [Client_name] <> cl
            )
        )
    ) + 0
RETURN
    IF ( c > 0, TRUE (), FALSE () )

 


Or as a measure:

 

Match_Found_Mes =
VAR cl =
    FIRSTNONBLANK ( ClientOrders[Client_name], 1 )
VAR sl =
    SELECTEDVALUE ( ClientOrders[Send_location], "123" )
VAR rl =
    SELECTEDVALUE ( ClientOrders[Receive_location], "123" )
VAR c =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( ClientOrders ),
            FILTER (
                ALL ( ClientOrders ),
                [Send_location] = sl
                    && [Receive_location] = rl
                    && [Client_name] <> cl
            )
        )
    ) + 0
RETURN
    IF ( c > 0, TRUE (), FALSE () )

 

 

Cheers


Edit: Ah, sorry @parry2k , I hadn't seen your answer.




Feel free to connect with me:
LinkedIn

View solution in original post

Highlighted
Frequent Visitor

Re: Checking for Partial Row Matches in the Same Table

Thanks for the quick response, I was thinking I'll probably need to convert the measure to a column. However the combining the relevant separate column values into a single colum was a great idea because it will simplify my searches later on. 

 

Thank you!

Highlighted
Frequent Visitor

Re: Checking for Partial Row Matches in the Same Table

@Smauro 

This is perfect, thank you!

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors