Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
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
Thanks, it helps!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |