Join two unrelated tables and return only the first row instance for data repeated in a column

Adrian Martinez II 25 Reputation points
2024-12-04T14:59:36.94+00:00

I am attempting to join two unrelated tables and return only one row instance for repeating data in a column.

TABLE A
User's image

TABLE B

User's image

DESIRED RESULT

User's image

EVALUATE
VAR A = 
    DISTINCT (
         CALCULATETABLE (
               SELECTCOLUMNS (
                    'TABLE A',
                     "COLUMN 01", 'TABLE A'[COLUMN 01] &""
                     // -COMMENT- ADDED TO JOIN WITH TABLE B
								)
						)
			)

VAR
	DISTINCT (
		CALCULATETABLE (
			SELECTCOLUMNS (
					'TABLE B'
					"COLUMN 01"
					// -COMMENT- ADDED TO JOIN WITH TABLE A
					"COLUMN 02", 'TABLE B'[COLUMN 02]
								)
						)
			)

VAR RESULT = 
	DISTINCT(
	NATURALLEFTOUTERJOIN( A,B )
			)

RETURN
	RESULT

ORDER BY [COLUMN 01] , [COLUMN 02] DESC
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,300 questions
0 comments No comments
{count} votes

Accepted answer
  1. ZoeHui-MSFT 38,956 Reputation points
    2024-12-05T01:47:40.9566667+00:00

    Hi Adrian Martinez II,

    Check below code:

    EVALUATE
    ADDCOLUMNS (
        'TABLE A',
        "COLUMN 02",
            CALCULATE (
                MAX ( 'TABLE B'[COLUMN 02] ),
                FILTER ( 'TABLE B', 'TABLE B'[COLUMN 01] = 'TABLE A'[COLUMN 01] )
            )
    )
    
    

    Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.