Measure combining two related tables with if then logic sought

[MichelBrown]

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.

[amitchandak · Accepted 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]

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.