topic Re: DAX to find highest matching column value in DAX Commands and Tips

DAX to find highest matching column value

Anonymous — Tue, 19 Jul 2022 20:07:23 GMT

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.

Re: DAX to find highest matching column value

vapid128 — Tue, 19 Jul 2022 19:22:51 GMT

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

Measure = DIVIDE(

COUNTROWS(TableNew),

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

)

Re: DAX to find highest matching column value

Anonymous — Tue, 19 Jul 2022 19:36:43 GMT

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

Re: DAX to find highest matching column value

tamerj1 — Tue, 19 Jul 2022 20:15:03 GMT

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

Re: DAX to find highest matching column value

Anonymous — Tue, 19 Jul 2022 20:30:01 GMT

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

You're incredible!

Re: DAX to find highest matching column value

Anonymous — Tue, 19 Jul 2022 20:37:48 GMT

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

Re: DAX to find highest matching column value

tamerj1 — Tue, 19 Jul 2022 20:49:28 GMT

Anonymous

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