Measure issue joining 2 tables

[Anonymous]

Hello,

I'm just starting with Power BI and I have an issue.

I uploaded 2 tables :

Table 1

Group ID	ID
GPID_A	ID_1
GPID_A	ID_1
GPID_A	ID_2
GPID_B	ID_3
GPID_C	ID_4

Group ID aggregates some IDs - It's possible to get several ID/Group ID

Table 2

ID	value
ID_1	10
ID_2	15
ID_3	5
ID_4	20

It exists a value for each ID - In this table there is only one ID (no duplicates)

I would like to have as a final result :

Expected Table

Group ID	ID	Value ID	Value Group ID
GPID_A	ID_1	10	35
GPID_A	ID_1	10	35
GPID_A	ID_2	15	35
GPID_B	ID_3	5	5
GPID_C	ID_4	20	20

Value ID is the ID value found in the Table 2 and Value Group ID is the value aggregrated by Group ID i.e for the 3 first lines (GPID_A) = 10 + 10 + 15

In Power BI I added a relationship between Table1 (ID) and Table2 (ID) with the following cardinality (*,1)

However when I add the value in my dashboard I obtain this result (it seems it's the total result without duplicates) :

Result in Power BI

Group ID	ID	Somme de Value
GPID_A	ID_1	50
GPID_A	ID_1	50
GPID_A	ID_2	50
GPID_B	ID_3	50
GPID_C	ID_4	50

Could you help me?

Thanks in advance

[Anonymous]

For the Value ID I found a solution :

Value ID =

CALCULATE (

SUM ( 'Table 2'[Value] ),

FILTER ( 'Table 2', 'Table 2'[ID Table 2] IN VALUES ( 'Table 1'[ID]) )

)

[speedramps]

Option 1:-

Use the the deduplication command in the Table1 query editor

create 1:m relationship to table2

Option 2:-

delete relationship from table1 to table2
copy table1 to DimID tabele

Use the the deduplication command in the DimID query editor

create 1:m relations to table1 and table2

I helped you now please help me with kudos.

Please click thumbs up and accepts as solution buttons.

One problem per ticket please.

If you you need to revise the problem then please accept this solution and raise a new ticket. 

Thanks ‌‌

[SpartaBI · Accepted solution]

@Anonymous 
step 1: create a relationship between the two:

Add these Calculated Columns to Table 1 (the table on the many side):

Value For ID = RELATED('Table 2'[value])

Total Group =

VAR _g = 'Table 1'[Group ID]

RETURN

CALCULATE(

SUM('Table 1'[Value For ID]),

REMOVEFILTERS('Table 1'),

'Table 1'[Group ID] = _g

)

This is the result:

In case it answered your question, please accept it as a solution to help the other members find it more quickly. Appreciate Your Kudos 💪
Showcase Report – Contoso By SpartaBI 
Website Linkedin Facebook 
This is SpartaBI!

[Anonymous]

It works. Thanks for your help