DAX to find highest matching column value

[Anonymous]

Hello!

I have two tables, one containing "Planned Routes" and other containing "Executed Routes". Problem is I don't have a persistent value in both columns to create a relationship.

Basically, I want to match the ID from the first table to a different ID on the second table (represented as "Letter"), by finding the highest matching client list from both.

In the example below, Route ID "1" equals Route Letter "A", because the match is 100%. But sometimes, some clients won't be delivered (location is closed), so it won't match perfectly. In this case, it should find the highest match possible and display its correspondent ID/Letter

EDIT: The steps (for route ID 1):

Compare Route ID 1 to Route Letter A and get its matching value (100%)

Compare Route ID 1 to Route Letter B and get its matching value (0%)

Compare Route ID 1 to Route Letter C and get its matching value (0%)

Evaluate the maximum value between them (100%) and return the correspondent Letter ("A").

The numbers won't always be 0 and 100%, because that might be planned routes that share the same customer and routes that won't be able to deliver all customers. I just need to know the highest matching among all. 

[tamerj1 · Accepted solution]

Hi @Anonymous 
Here is a proposed solution https://we.tl/t-RvZP2J3nLr

Matching = 
VAR PlannedClients = VALUES ( Planned[Client] )
RETURN
    MAXX ( 
        SUMMARIZE ( Executed, Executed[Route Letter] ),
        VAR ExecutedClients = CALCULATETABLE ( VALUES ( Executed[Client] ) )
        VAR CommonClients = INTERSECT ( PlannedClients, ExecutedClients )
        RETURN
            DIVIDE ( 
                COUNTROWS ( CommonClients ),
                COUNTROWS ( PlannedClients )
            )
    )

[Anonymous]

Is there a way to bring the "Route Letter" in this case as a column in the planned table? So I can build the relationship around it and make other calculations

[tamerj1]

@Anonymous 

It may be possible but I'm afraid that this will result in a circular dependency error. 

[Anonymous]

This is simply AMAZING! My mind is twisting and turning around this solution.

You're incredible!

[vapid128]

TableNew = DISTINCT( UNION( VALUES(Table11[Column2]),VALUES(Table22[Column2])))

Measure = DIVIDE(

COUNTROWS(TableNew),

CALCULATE(COUNTROWS(TableNew),REMOVEFILTERS(TableNew[Rount Letter]))

)

[Anonymous]

Problem is that a client can be present in multiple Routes (differing by day to day). 

Is the group of all clients from a planned route being comparable to an executed route that lets me find the correct match