Forum Discussion

New Member

2 years ago

Solved

Required Unique data when 2 tables are merged

Hi, I have below 2 tables - SOH table (no unique rows) & Order (no unique rows) SOH: Item# SOH Site Location A 6 XY A 3 OO B 9 XY B 12 DD Order: ...

rajendraongole1
2 years ago
Hi vkoukuntla - Ok , Can you please try the below one

it works
Total SOH =
VAR SelectedSite = SELECTEDVALUE('Ordert'[Site Location])
RETURN
IF(
ISBLANK(SelectedSite),
CALCULATE(
SUMX(
VALUES('Ordert'[Item#]),
CALCULATE(SUM('Ordert'[SOH]))
)
),
CALCULATE(
SUM('Ordert'[SOH]),
'Ordert'[Site Location] = SelectedSite
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

New Member

2 years ago

Thank you Rajendra, however when you unselect XY site, the total SOH for item A should be 9, in your example it shows qty: 18

rajendraongole1
Super User
2 years ago
Hi vkoukuntla - Ok , Can you please try the below one

it works
Total SOH =
VAR SelectedSite = SELECTEDVALUE('Ordert'[Site Location])
RETURN
IF(
ISBLANK(SelectedSite),
CALCULATE(
SUMX(
VALUES('Ordert'[Item#]),
CALCULATE(SUM('Ordert'[SOH]))
)
),
CALCULATE(
SUM('Ordert'[SOH]),
'Ordert'[Site Location] = SelectedSite
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Irwan
Super User
2 years ago
hello vkoukuntla

this is not merging two table, instead using measure.

the merge table and Order table are looking similarly.

1. Create a new measure with following DAX:
SOH =
var _Item = SELECTEDVALUE('Table 2'[Item#])
Return
SUMX(FILTER('Table 1','Table 1'[Item#]=_Item),'Table 1'[SOH])

2. Plot 'Order' data in Table visual including measure above with 'Site Location' slicer.

Hope this will help you.
Thank you.