Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

SUMX and FILTER

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

 

 

  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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]
    )

     

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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]
    )

     

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    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,