SUMX and FILTER

[PowerRon]

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

[PowerRon]

Thnx @bhanu_gautam , this seems to work right

[DataNinja777]

Hi @PowerRon ,

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,

[bhanu_gautam · Accepted solution]

@PowerRon , 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]
)