Get count of a column for every asset based off of a timestamp that's unique to each asset

[Fulfilled]

Here is my problem:

Lets say i have 10 machines that accept bills and coupons.  Those machines have a capacity to hold 500 bills.  I want to setup a dashboard that will inform me of the bill count of each of those machines so that they dont fill up.  My data is as follows:

Table 1

Asset # (AssetID)

Transaction codes (Bill accepted, Cash Emptied) (TXCode)

• 11 (Bill Accepted)
• 12 (Coupon Accepted)
• 13 (Cash Emptied)

Timestamp (TransDate)

Example:

AssetID	TXCode	TransDate
1	11	7/1/2023 12:13pm
2	11	7/1/2023 12:12pm
3	12	7/1/2023 12:11pm
1	13	7/1/2023 12:10pm
2	13	7/1/2023 12:05pm
3	13	7/1/2023 12:01pm

From this table i am able to grab a timestamp for when the cash was last emptied, hence resetting the count of bills accepted since that last event.

I have tried many different ways to come up with the total count of TXCodes since last "Cash Emptied" TX Code, however everything i come up with seems to lead to a memory crunch when trying to calculate it.  My latest attempt consisted of creating a 2nd table with 1 row for each Asset.  On that table i brought in the "Cash Emptied" DateTime timestamp for each asset.  I then tried to leverage that value in the following formula thinking that would be less calculations needed in the formula so maybe it would work quicker, but it led to the same result. Here is the latest DAX formula i tried as well as the info in new table i am leveraging:

Table 2

Asset # (AssetID)
Cash Emptied Timestanp (CashEmptied)

Table2 Example:

AssetID	CashEmptied
1	7/1/2023 12:10pm
2	7/1/2023 12:05pm
3	7/1/2023 12:01pm

=

VAR _Emptied = CALCULATE(MAX(Table2[CashEmptied]), ALLEXCEPT(Table2, Table2[AssetID]))

Return

CALCULATE(COUNT(Table1[TXCode]) , ALLEXCEPT(Table2, Table2[AssetID]) ,
KEEPFILTERS(Table1[TXCode] = "11" || Table1[TXCode] = "2" ) ,

TIME(
HOUR('Table1'[TransDate]),
MINUTE('Table1'[TransDate]),
SECOND('Table1'[TransDate])
) >= TIMEVALUE( _Emptied )
)

After this failed, i tried creating a column labeled "Inserted" in table 1 using an IF statement:

Inserted =

IF((Table1[TXCode] = "11") || (Table1[TXCode] = "12") , 1 , 0)

I figured i would remove the "filter" aspect out of the query itself again to try and minimize the calculations needed in the single query to hopefully avoid the memory crunch issue. I then tried to SUM up all the values in the Inserted column after the "CashEmptied" associted with each asset. This was my query:

=

VAR _Emptied = CALCULATE(MAX(Table2[CashEmptied]), ALLEXCEPT(Table2, Table2[AssetID]))

Return

CALCULATE(SUM(Table1[TXCode]) , ALLEXCEPT(Table2, Table2[AssetID]) ,

TIME(
HOUR('Table1'[TransDate]),
MINUTE('Table1'[TransDate]),
SECOND('Table1'[TransDate])
) >= TIMEVALUE( _Emptied ),

)

However i ran into the same issue with memory. At this point i am unsure how to accomplish this query in an efficient way to achieve my goal. Was hoping someone could help me rework my certainly poor DAX formula to give me a solution to this problem. I appreciate your help in advance!!

[johnt75]

Try

Num bills =
IF (
    ISINSCOPE ( 'Table'[AssetID] ),
    VAR CurrentAsset =
        SELECTEDVALUE ( 'Table'[AssetID] )
    VAR LastEmptied =
        CALCULATE ( MAX ( 'Table'[Trans date] ), 'Table'[TX Code] = 13 )
    VAR Result =
        CALCULATE (
            COUNTROWS ( 'Table' ),
            'Table'[TX Code] IN { 11, 12 },
            'Table'[Trans date] > LastEmptied
        )
    RETURN
        Result
)