one to many relationship count records that are not in the second table

[Anonymous]

Hi community experts, I have two tables, T1 and T2 with a relatinship one-to-many. Something like this

Table 1

ID	Attribute
1	0.5
2	1
3	1
4	0.8
5	0.8

Table 2

ID	Field2
1
1
1
3
3
5
5

Now, my relationship is based on the ID field. I also have a measure that sums the Attribute in Table 1.

How can I use this measure saying something like: Calculate(sum(Table1[Attribute]); Table2[ID] = blank()) which does not work.

I'd like to calculate that measures, to see how much the sum would be for the records that do not have any correspondent in Table2.

Any idea?

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

You can use expect function to compare and extract nonexistent records from two tables:

Measure =
VAR _list =
    EXCEPT ( VALUES ( Table1[ID] ), VALUES ( Table2[ID] ) )
RETURN
    CALCULATE (
        SUM ( Table[Attribute] ),
        FILTER ( ALLSELECTED ( Table1 ), [ID] IN _list )
    )

Regards,

Xiaoxin Sheng

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

You can use expect function to compare and extract nonexistent records from two tables:

Measure =
VAR _list =
    EXCEPT ( VALUES ( Table1[ID] ), VALUES ( Table2[ID] ) )
RETURN
    CALCULATE (
        SUM ( Table[Attribute] ),
        FILTER ( ALLSELECTED ( Table1 ), [ID] IN _list )
    )

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Thanks, it helps!