topic Re: DAX measure that calculates the list of transactions whose SUM is greater than and less than $X in DAX Commands and Tips

DAX measure that calculates the list of transactions whose SUM is greater than and less than $X

phangri — Thu, 20 Jul 2023 17:31:42 GMT

Hi everyone,

Let's say that I have the following Fact Table and also a Calendar Table.

Date	Account	Description	Amount
5/5/2023	123	Legal	100k
5/6/2023	123	Legal	200k
5/6/2023	124	Fees	25k
5/7/2023	124	Legal	100k
5/8/2023	123	Fees	50k
5/9/2023	124	Incoming Transfer	(270k)

The goal is to return the list of transactions whose SUM is greater than 250k or less than -250k.

Let's say that I selected a range of dates from 5/5/2023 to 5/7/2023 in my slicer. I expect the following result:

Date	Account	Description	Amount
5/5/2023	123	Legal	100k
5/6/2023	123	Legal	200k

I have this Dax formula but it excludes transactions less than 250k and I need the group of transactions that contribute to the condition.

FilteredTransactions =

VAR TotalTransactions = SUM('Cash Activity'[Transaction])

RETURN

IF(

OR(

TotalTransactions > 250000,

TotalTransactions < -250000

'Cash Activity'[Transactions],

BLANK()

)

Any idea how to achieve this?

Thank you in advance,

Re: DAX measure that calculates the list of transactions whose SUM is greater than and less than $X

Greg_Deckler — Thu, 20 Jul 2023 18:28:01 GMT

phangri Can you explain the logic in how you are arriving at your expected output because I'm not following.

Re: DAX measure that calculates the list of transactions whose SUM is greater than and less than $X

phangri — Thu, 20 Jul 2023 22:00:21 GMT

Hi Greg_Deckler

I arrive at this result given that in the date range 5/5/2023 to 5/7/2023 the only account that in the sum of transactions is > 250k is the account "123", therefore it shows the transactions that contribute to that sum.

Let's say I'm selecting the range 5/7/2023 to 5/9/2023, the expected value would be BLANK given that:

5/7/2023 - Acct 124 - Legal - $100k
5/8/2023 - Acct 123 - Fees - $50k
5/9/2023 - Acct 124 - Incoming Transfer - $(270k)

Acct 124 = (170k)
Acct 123 = 50k

The sum of the transactions in that period of time does not meet the condition, so the list of transactions that occurred in that period would not be displayed.

Thank you!