topic New table using values from two other tables in DAX Commands and Tips

New table using values from two other tables

Anonymous — Mon, 08 May 2023 00:20:08 GMT

Hello,

I am working on analysing businesses' financial statements and need some help with writing DAX code to make a new table using values from another table, and names from another table. I already posted a similar question ( ) but my problem has gotten a bit more complex so the solution there doesn't apply to this. Maybe Mahesh0016 has an answer for this as well? 😄

Say I have two tables, Table 1 and Table 2.

Table 1 (which has info on shop names, year, the original line item name from each shop's financial statements and the value corresponding to that line item)

Shop name	Year	Line item name	Value
Shop A	2022	current assets total	10
Shop A	2022	non-current assets total	20
Shop A	2022	current liabilities	30
Shop B	2022	CA total	40
Shop B	2022	NCA total	50
Shop B	2022	CL total	60
Shop B	2021	CA total	70
Shop B	2021	NCA total	80
Shop B	2021	CL total	90

Table 2 (which has the original line item name and a new name for consistency)

Line item name	New name
current assets total	Total current assets
CA total	Total current assets
non-current assets total	Total non-current assets
NCA total	Total non-current assets
current liabilities total	Total current liabilities
CL total	Total current liabilities

I want to use information from Tables 1 and 2 to ultimately get a table like this (let's call it Table 3):

Table 3

Shop name	Year	Total current assets	Total non-current liabilities	Total current liabilities
Shop A	2022	10	20	30
Shop B	2022	40	50	60
Shop B	2021	70	80	90

Many thanks in advance!

Re: New table using values from two other tables

FreemanZ — Mon, 08 May 2023 01:11:49 GMT

hi Anonymous

try to plot a matrix visual directly, like:

or?

Re: New table using values from two other tables

Anonymous — Mon, 08 May 2023 01:16:09 GMT

Thanks, that's a good idea! But I would prefer to do it without having to plot a visual....is there a way to just use DAX to make the table?

Reason is I need this table to add to my data model. If I were to plot a matrix visual I'd have to save it as a csv and then load that into my model which I'd rather avoid doing.

Re: New table using values from two other tables

FreemanZ — Mon, 08 May 2023 01:49:09 GMT

hi Anonymous

something like this?

Table3 = ADDCOLUMNS( SUMMARIZE( Table1, Table1[Shop Name], Table1[Year] ), "Total current assets", CALCULATE( SUM(Table1[Value]), Table2[New Name]="Total current assets" ), "Total non-current assets", CALCULATE( SUM(Table1[Value]), Table2[New Name]="Total non-current assets" ), "Total current liabilities", CALCULATE( SUM(Table1[Value]), Table2[New Name]="Total current liabilities" ) )

Re: New table using values from two other tables

Anonymous — Mon, 08 May 2023 03:08:02 GMT

Thanks so much!