cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Hello everyone

Let's say 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 Rates 25k
5/7/2023 124 Legal 100k
5/8/2023 123 Rates 50k
5/9/2023 124 Incoming Transfer (270k)

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

Let's say I selected a date range from 5/5/2023 to 5/7/2023 in my Slicer. The following output is expected:

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

If for example I selected the range 5/7/2023 to 5/9/2023 my expected result would be BLANK given the following:

5/7/2023 124 Legal 100k
5/8/2023 123 Rates 50k
5/9/2023 124 Incoming Transfer (270k)

Account 124 sum (\$170k)

Count 123 sum 50k

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

FilteredTransactions =
VAR TotalTransactions = SUM('Cash Activity'[Transaction])
RETURN
IF(
OR(
TotalTransactions > 250000,
TotalTransactions < -250000
),
'Cash Activity'[Transactions],
BLANK()
)

Any idea how to achieve this?

2 REPLIES 2
Super User

Try this

``````Accounts Matching =
CALCULATE(SUM('Table'[Amount]))), "MatchCheck", [sumamt] >= 250000 || [sumamt] <= -250000)

var c = COUNTROWS(summaryTable) -- Testing! ... RETURN C

return if ( HASONEVALUE('Table'[Account])
, If( CONTAINS(summaryTable, [MatchCheck], TRUE(), 'Table'[Account], SELECTEDVALUE('Table'[Account])), 1, 0)
, blank() )``````

Sample scenarios:

Hope this helps!

Super User

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors