Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two tables:
How do I write a calculated column in table A, that will sum up the value in table B but only for rows where the accountIDs match and increment = 1?
I've made various attempts and my best one is returning a value where I know for sure there should not be a match and it should be returning a blank.
=CALCULATE(Sum(tableB[Value]),All(tableB[accountID])=tableA[accountID],tableB[Increment]=1)
Solved! Go to Solution.
Ok I think I've solved it:
=CALCULATE(Sum(tableB[Value]),Filter(ALL(tableB),tableB[AccountID]=tableA[AccountID]&&[Increment]=1))
Hello @Anonymous
You can use TREATAS to create the vitual relationship.
Column =
CALCULATE (
SUM ( TableB[Value] ),
TREATAS ( VALUES ( TableA[AccountID] ), TableB[AccountID] ),
TableB[Increment] = 1
)
Hello @Anonymous
You can use TREATAS to create the vitual relationship.
Column =
CALCULATE (
SUM ( TableB[Value] ),
TREATAS ( VALUES ( TableA[AccountID] ), TableB[AccountID] ),
TableB[Increment] = 1
)
Ok I think I've solved it:
=CALCULATE(Sum(tableB[Value]),Filter(ALL(tableB),tableB[AccountID]=tableA[AccountID]&&[Increment]=1))
HI @Anonymous,
You can try to use allselected function to extract value from other table(even if they do not exist relationship) and stored in a variable, then you can compare with current table fields and variable to filter correspond records. (it should works on both measure and calculate column expressions)
formula =
VAR idList =
ALLSELECTED ( tableA[AccountID] )
RETURN
CALCULATE (
SUM ( tableB[Value] ),
FILTER ( ALLSELECTED ( tableB ), tableB[AccountID] IN idList && [Increment] = 1 )
)
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT
The IN operator in DAX
Regards,
Xiaoxin Sheng
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |