Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Power BI community 🙂
I have this relationship, dim_accounting_accounts ( id ) is related to fact_accounting_transactions ( credit_account_id (active) & debit_account_id (deactive))
I want to SUM fact_accounting_transaction ( amount ) where dim_accounting_accounts ( account_type_id ) is X for fact_transaction_accounts (credit_account_id) and dim_accounting_accounts ( account_type_id ) is Y for fact_transaction_accounts (debit_account_id).
any suggestions?
Solved! Go to Solution.
Hi @odzelashvili1 ,
Please try the measure.
Measure =
VAR tab1 =
CALCULATETABLE (
fact_accounting_transactions,
dim_accounting_accounts[account_type_id] = "x"
)
VAR tab2 =
CALCULATETABLE (
fact_accounting_transactions,
dim_accounting_accounts[account_type_id] = "y",
USERELATIONSHIP ( dim_accounting_accounts[id], fact_accounting_transactions[debit_account_id] )
)
RETURN
CALCULATE (
SUM ( fact_accounting_transactions[amount] ),
INTERSECT ( tab1, tab2 )
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @odzelashvili1 ,
Please try the measure.
Measure =
VAR tab1 =
CALCULATETABLE (
fact_accounting_transactions,
dim_accounting_accounts[account_type_id] = "x"
)
VAR tab2 =
CALCULATETABLE (
fact_accounting_transactions,
dim_accounting_accounts[account_type_id] = "y",
USERELATIONSHIP ( dim_accounting_accounts[id], fact_accounting_transactions[debit_account_id] )
)
RETURN
CALCULATE (
SUM ( fact_accounting_transactions[amount] ),
INTERSECT ( tab1, tab2 )
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I can do this with this way, but I want not to add calculate columns to my tables:
aa_debit_account =
CALCULATE (
MAX ( dim_accounting_accounts[account_type_id] ),
FILTER (
dim_accounting_accounts,
dim_accounting_accounts[id] = fact_accounting_transactions[debit_account_id]
),
USERELATIONSHIP ( dim_accounting_accounts[id], fact_accounting_transactions[debit_account_id] )
)
aa_credit_account =
CALCULATE(
MAX(dim_accounting_accounts[account_type_id]),
FILTER(
dim_accounting_accounts, dim_accounting_accounts[id] = fact_accounting_transactions[credit_account_id]))
and after this Sum (amount) where aa_credit_account = x and aa_debit_account = y.
@odzelashvili1
The active relationship will do that automatically. Did you try my code? it should give the desired results.
Please try
=
VAR SumCredit =
SUM ( fact_accounting_transaction[amount] )
VAR SumDebit =
CALCULATE (
SUM ( fact_accounting_transaction[amount] ),
USERELATIONSHIP ( dim_accounting_accounts[id], fact_transaction_accounts[debit_account_id] )
)
RETURN
SumCredit + SumDebit
This is not what I meant.
I want to sum (amount) where credit_account_id = x and debit_account_id = y same time. But this x and y must be filtered by another table (dim_accounting_accounts) which is related to this debit and credit columns.
@odzelashvili1
Can you advise how your visual look like? How do you select X and Y? Or you just want to hard code certian values of X and Y?
User | Count |
---|---|
93 | |
83 | |
77 | |
72 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |