topic Show values in a matrix from two different tables based on a special ID column in each table in DAX Commands and Tips

Show values in a matrix from two different tables based on a special ID column in each table

An0n — Fri, 03 Feb 2023 20:23:40 GMT

I'm trying to show values in a matrix from two different tables based on an ID column in text format. This ID column does not match fully between the two tables and one ID can still have multiple rows attached to it.

Table 1

ID	Column 3	Column 4	Column 5	AMOUNT Table 1
AFG137B08	xyz	1277	US	4
BFD159B08	xyz	1233	EU	15
BFD159B08	abc	1245	DE	12
CCC096A22	def	1233	US	9
XTU544L51	xyz	1245	EU	6

Table 2

ID	Column 3	Column 4	Column 5	AMOUNT Table 2
AFG137B08	xyz	1277	US	7
BFD159B08	xyz	1233	EU	20
CCC096A22	abc	1245	DE	3
BVN213H78	def	1233	US	14
AFG137B08	bte	1245	US	13

Desired Matrix in visuals

ID	AMOUNT Table 1	AMOUNT Table 2	Difference AMOUNT Table 1 and Table 2
AFG137B08	4	20	-16
BFD159B08	27	20	7
CCC096A22	9	3	6
BVN213H78	0	14	-14
XTU544L51	6	0	6

Hope this helps to describe the problem.

Thanks in advance!

Re: Show values in a matrix from two different tables based on a special ID column in each table

FreemanZ — Fri, 03 Feb 2023 23:15:47 GMT

hi An0n

one way of implementation is to create sum tables each then you can join them and then calculate. Like:

SumTable1 =

ADDCOLUMNS

VALUES(Table1[ID],

"Amount1",

CALCULATE(SUM(Table1[Amount]))

)

Re: Show values in a matrix from two different tables based on a special ID column in each table

Anonymous — Mon, 06 Feb 2023 02:39:14 GMT

Hi An0n ,

I create a sample pbix file(see the attachment), please check if that is what you want.

1. Create a dimension table with IDs which is from both Table 1 and Table 2

IDs = DISTINCT ( UNION ( VALUES ( 'Table 1'[ID] ), VALUES ( 'Table 2'[ID] ) ) )

2. Create the measures as below to get the sum of amount in Table 1 and Table 2, the difference of amount

Amount_t1 = VAR _selid = SELECTEDVALUE ( 'IDs'[ID] ) RETURN CALCULATE ( SUM ( 'Table 1'[AMOUNT Table 1] ), FILTER ( 'Table 1', 'Table 1'[ID] = _selid ) ) + 0

Amount_t2 = VAR _selid = SELECTEDVALUE ( 'IDs'[ID] ) RETURN CALCULATE ( SUM ( 'Table 2'[AMOUNT Table 2] ), FILTER ( 'Table 2', 'Table 2'[ID] = _selid ) ) + 0

Difference of Amount = [Amount_t1]-[Amount_t2]

3. Create a table visual as below screenshot

Best Regards

Re: Show values in a matrix from two different tables based on a special ID column in each table

An0n — Tue, 07 Feb 2023 16:18:55 GMT

Thank you Anonymous this solution worked for me.

May I have a short follow-up request?

How do I show the correct column totals? They currrently show zero on my end, as also in your example.

Appreciate your help, thanks in advance!

Re: Show values in a matrix from two different tables based on a special ID column in each table

Anonymous — Fri, 03 Mar 2023 06:49:49 GMT

Hi An0n ,

I updated the sample pbix file(see the attachment), please check if that is what you want.

1. Create another two measures as below to replace the measure [Amount_t1] and [Amount_t2] onto the visual

Measure = SUMX(VALUES('IDs'[ID]),[Amount_t1])

Measure 2 = SUMX(VALUES('IDs'[ID]),[Amount_t2])

2. Update the formula of measure [Difference of Amount] as below

Difference of Amount = [Measure]-[Measure 2]

Best Regards