cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ritu24raj
Helper II
Helper II

Combining values from two tables into one

Hi -

 

I need some help, in regards to merge the values from two different tables based on one value.

Table 1

ritu24raj_0-1603811687009.png

Table 2

ritu24raj_1-1603811714326.png

As we can see the Revenue In and Revenue Out values are different in both the tables. Now, when I creating a resultant table both the values are appearing to be the same.

 

Resultant Table

ritu24raj_2-1603811785735.png

The values of the Delivery region and PO contract region are the same. I tried creating a DAX to get the revenue out values in the table.

 

DAX -

Revenue Out = 

CALCULATE(SUMX(Registration,[Order USD Amount Total]),

FILTER(Class,[Cross Region Transfer1]="Yes" || [Cross Region Transfer2]="Yes"),

ALLSELECTED(Registration[Purchase Order Contract Region])

)
1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @ritu24raj , 

 

Since you are using SSAS >> Live connection, it is suggested to create a new table "Region", which contains regions both from "PO Contract Region" and "Delivery Region",  and create relationships among other tables in SSAS.

 

 

Best Regards,

Icey

 

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

View solution in original post

5 REPLIES 5
ritu24raj
Helper II
Helper II

HI @niveknonrev - This is not working as expected.

  1. Revenue In and Revenue Out are two different measures
  2. Delivery Region and PO Region are coming from two separate objects

 

Icey
Community Support
Community Support

Hi @ritu24raj , 

 

Since you are using SSAS >> Live connection, it is suggested to create a new table "Region", which contains regions both from "PO Contract Region" and "Delivery Region",  and create relationships among other tables in SSAS.

 

 

Best Regards,

Icey

 

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

View solution in original post

niveknonrev
Frequent Visitor

Can you just use summarize to create the new resultant table with the RevIN data then add a column with a value filter?

 

 

ResultTable = SUMMARIZE(testdata2,TestData2[Delivery Reg],TestData2[RevIN])
NewRevOutColumn = CALCULATE(values(TestData1[Revout]),FILTER(TestData1,ResultTable[Delivery Reg]=TestData1[PO]))
 

Capture.PNG

FrankAT
Super User II
Super User II

Hi @ritu24raj 

you didn't describe the relationships of your data model, so here is what might be step further:

 

27-10-_2020_17-48-07.png

 

Sum of Revenue Out = SUM('Table Rev Out'[Revenue Out])

Sum of Revenue In = SUM('Table Rev In'[Revenue In])

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Hi @FrankAT -

 

Thanks for this, I am using SSAS >> Live connection to cube to connect to Power BI and I can't create manual relationships in the backend.

 

The only thing I can do is w/ the help of DAX

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.