Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello all,
Can anyone help me with the following:
I have a fact table with (simplified) many rows with account number and amount.
And a dimension table with account number, description and DebetCredit indicator
In the fact table amounts are negative on a row with a (related) credit account and positive on a row with a (related) debet account.
For the sake of graphs, I want to change the credit / negative amounts into positive amounts. My question is how to do this with a DAX measure?
My thinking was something like this:
VAR multiplier = If (RELATED(dim_Accounts[DebitCredit])="Credit";-1;1)
RETURN
Sum(fact_SAP[Amount]) * multiplier
But this turns out to be wrong in such a way that even the help error text doesn't help me to find a solution. My gut feeling says I have to use RELATED or maybe even RELATEDTABLE ?? and maybe something like SUMX but I can't figure it out.
Thanks in advance for any support.
Solved! Go to Solution.
@MichelBrown , try like
calculate(-1*Sum(fact_SAP[Amount]),filter(dim_Accounts,dim_Accounts[DebitCredit])="Credit") + calculate(Sum(fact_SAP[Amount]),filter(dim_Accounts,dim_Accounts[DebitCredit])<>"Credit")
@MichelBrown , try like
calculate(-1*Sum(fact_SAP[Amount]),filter(dim_Accounts,dim_Accounts[DebitCredit])="Credit") + calculate(Sum(fact_SAP[Amount]),filter(dim_Accounts,dim_Accounts[DebitCredit])<>"Credit")
Hello amitchandak,
Thank you for your quick reply and working solution. I didn't think of this completely other solution and was looking in another direction.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
11 | |
10 | |
8 |
User | Count |
---|---|
62 | |
20 | |
11 | |
11 | |
11 |