topic Re: SUMX and FILTER in DAX Commands and Tips

SUMX and FILTER

Anonymous — Mon, 10 Feb 2025 14:28:29 GMT

Hi,

Let me simplify my question.
Besides my Date table I have three other tables.

Fact table, containing:

account-id
transaction-date
debit-currency-key
credit-currency-key

dim_debit_currency, containing:

debit-currency-key
debit-currency-code
debit-currency-name

dim_credit_currency, containing:

credit-currency-key
credit-currency-code
credit-currency-name

relations:

dim-debit-currency to fact 1:n, based on debit-currency-key
dim-credit-currency to fact 1:n, based on credit-currency-key
Date table to fact 1:n, based on transaction-date

I have one measure : Number of Trx = SUM(transaction-count)

Now I want to create a measure that gives me, no matter in what filter context used, the number of transactions where credit-currency-code <> debit-currency-code.

Can someone help?

Regards
Ron

Re: SUMX and FILTER

bhanu_gautam — Mon, 10 Feb 2025 14:50:15 GMT

Anonymous , Try using

Number of Trx with Different Currencies =
SUMX(
FILTER(
Fact,
RELATED(dim_debit_currency[debit-currency-code]) <> RELATED(dim_credit_currency[credit-currency-code])
),
Fact[transaction-count]
)

Re: SUMX and FILTER

DataNinja777 — Mon, 10 Feb 2025 15:00:14 GMT

Hi Anonymous ,

My observation is that your fact table doesn't contain amount column but the accounting records should have matching debits and credits which should balance (add up) to zero, and currency should be the same for the given transactions. I am trying to understand which situation your particular requirement arises, as your accounting system will record the transaction in document currency (transaction currency) which will be converted automatically to your local currency. I'd greatly appreciate it if you could let me know why your fact table doesn't have the amount field.

Best regards,

Re: SUMX and FILTER

Anonymous — Tue, 11 Feb 2025 08:26:37 GMT

Thnx bhanu_gautam , this seems to work right