Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Comparing two sets of columns from two tables

Hi,   Currently I have two tables that have addresses and dates. Table 1 looks likes this   Address Date 123 green st 1/2/21 456 red ave 2/3/21 789 purple ter 3/4/21 101 or...
Need Help
Tips and Tricks
Tutorial Requests
  • edhans's avatar
    edhans
    4 years ago

    use a merge. In this example, TableA is the top, TableB is the bottom. Table Analysis is just a reference to TableA (you said you needed a 3rd table). Then I merged TableA and B on the address and returned the date from B, then did the comparison on the dates.

     

     

    Here is my Excel file so you can look at the entire code and how it works.

smpa01's avatar
smpa01
Community Champion
4 years ago

Anonymous  DAX solution is atatched

Measure1 = 
//with relationship
VAR _maxAddresst2 =
    CALCULATE ( MAX ( tbl2[date] ), tbl1 )
VAR _maxAddresst1 =
    MAX ( tbl1[Date] )
RETURN
    IF (
        MAX ( tbl1[Address] ) <> BLANK (),
        IF ( _maxAddresst2 <= _maxAddresst1, "Signed", "Not Signed" )
    )

Measure2 = 
//with or without relationship
VAR __maxAddresst2 =
    CALCULATE (
        MAX ( tbl2[date] ),
        TREATAS ( VALUES ( tbl1[Address] ), tbl2[address] )
    )
VAR _maxAddresst1 =
    MAX ( tbl1[Date] )
RETURN
    IF (
        MAX ( tbl1[Address] ) <> BLANK (),
        IF ( __maxAddresst2 <= _maxAddresst1, "Signed", "Not Signed" )
    )