Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 @Anonymous - 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.

Anonymous
Not applicable

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
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.