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
Anonymous
Not applicable

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

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

Table 1

IDAttribute
10.5
21
31
40.8
50.8

 

Table 2

IDField2
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?

2 ACCEPTED SOLUTIONS
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Thanks, it helps!

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Thanks, it helps!

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.