Dax merge two table together then sum on 2 columns

[Ali1414]

need some help with the problem:

I have two tables, table A and table B. Table A is Sale Data and Table B is standard loadable of Car.

I want to Add the column "STD" from table B to table A without using Power Query and using the DAX Measure ("Vehicle" is unique on both Table) .then Group Table A with "Date" and "Vehicle" and sum on "STD".The final table should be as shown below:

What should i do?

[v-weiyan1-msft]

Hi @Ali1414 ,

nice method! Thank you, @Daniel29195 , for your quick response and the solution provided.

My additions are as follows, please refer to the following steps:

Please try the following code to create Calculated Column.

STD = RELATED('Table B'[STD])

Then use the following code to create a Measure.

Total_STD = CALCULATE(SUM('Table A'[STD]),
                      GROUPBY('Table A','Table A'[Date],'Table A'[Vehile])
                     )

Or You can set the aggregation method for STD field to sum.

Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ali1414 · Accepted solution]

While thanking, I only want to Solve this problem with "Measure". no with "calculated column" or "power query". I already solved this Problem by using them

[Ali1414]

I want to Solve this problem with "Measure"

[Ashish_Mathur]

Hi,

Create a third table with a unique list of all vehicles.  Create a relationship (Many to One and Single) from the Vehicle column of Tables A and B to this third table.  Create a Calendar Table with a relationship (Many to One and Single) from the Date column of Table A to the Date column of the Calendar Table.  To your visual, drag Vehicle from the third table and Date from the Calendar Table.  Write this measure

Measure = sum(TableA[STD])

Does this work?

[Daniel29195]

@Ali1414 

if vechile is unique in the 2 tables,, 

then this should be a 1 to 1 relationship .

so it is better to merge the 2 tables on vehicule  in power query , unless your requirements need to have both tables a seperated entities.

you simply select the column the  you want to use for the merge . in your case ( vehicule from table A and vehicule from table B ) 

then you click on the button as above,  and you choose the column ( STD )  to get to the tableA . 

if you can t merge the 2 tables , 

what you can do, create a relationship between the 2 tables on vehicule  column, and create a calculated column in table A =  related ( tableB[std])

let me know is this works for you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Ali1414]

Power Query cannot be used due to the large amount of data (26,000,000 row). i want to solve this problem with DAX Because of increasing speed and reducing volume. Please help me in this regard

[Daniel29195]

@Ali1414 

what you can do, create a relationship between the 2 tables on vehicule  column, and create a calculated column in table A =  related ( tableB[std])

does this works for you  ? 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Ali1414]

I made the mistake of an acceptable solution

when change Relationship I encounter this error

[Daniel29195]

@Ali1414 

when you create the link , what is the rlation between the 2 tables ? 

are you able to share the file so i can help you out ? 

best regards

[Ali1414]

The data volume is too large (140 Mb). This image is the real Relationship page:

I had converted this image to the above tables for easier understanding, and that's why it's a little different from it

[Daniel29195]

@Ali1414 

if you want to get the column from truckcoding to Data .

create nmew column in Data table  =  related(Truckcoding[col_name])

if you want to get data from Data to truckcoding : 

create a new column in truckcoding =  relatedtable(data)
relatedtable returns a table .

so you need do something like example  :

sumx ( relatedtable(data) , data[col_name_you_want_to_sum])

hope this helps you 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Ali1414]

 I encounter this error