filter table to MAX row of account

[GunnerJ]

In "VAR __Table4" I'm filtering table 3. However I need to add one more filter that I'm just a bit lost on. For each account I only want to look at the row the the "max" ROWRANK (circled in blue on right). For the example below I'd only want to see the row with the rowrank of 10. How can I edit table4 or what step can I add to make it look for and filter to only show each account's max rowrank?

Total Actives6 = 
VAR vMAXDATE = MAX('Date Table'[Date])

VAR __Table0 = 
    CALCULATETABLE(
        'MASTER CHARGE ACTIONS',
        ALLEXCEPT('MASTER CHARGE ACTIONS','MASTER CHARGE ACTIONS'[BI_ACCT]),'MASTER CHARGE ACTIONS'[CHG_DATE] <= vMAXDATE
    )

VAR __Table1 = 
    CALCULATETABLE(
        __Table0,
        'MASTER CHARGE ACTIONS'[CHG_DATE]<=vMAXDATE
    )

VAR __Table2 =
    SUMMARIZE(__Table1,'MASTER CHARGE ACTIONS'[BI_ACCT],"CHG_DATE",max('MASTER CHARGE ACTIONS'[CHG_DATE]))

VAR __Table3 =
    NATURALINNERJOIN(__Table1,__Table2)

VAR __Table4 = filter(__Table3,'MASTER CHARGE ACTIONS'[Account Status]="Active" && 'MASTER CHARGE ACTIONS'[CHG_DATE] <= vMAXDATE) 

VAR __Table5 =
    DISTINCT( 
        SELECTCOLUMNS(__Table4,"Account",'MASTER CHARGE ACTIONS'[BI_ACCT])
    )

RETURN
    CALCULATE(
        COUNTX(
            __Table5,[Account]
        )
    )

 Any help is appreciated and please let me know if anything additional info is needed. 

Thanks!

[johnt75]

I think you can combine GENERATE with TOPN to get just the top row for each account. You'd need to wrap the TOPN in a SELECTCOLUMNS to get all columns excluding the account number, otherwise you would get an error with 2 columns having the same name, but something like

var _FilteredTable = GENERATE( VALUES(_Table4[BI_ACCT]),
SELECTCOLUMNS( TOPN(1, _Table4, _Table4[ROWRANK]),
"Column1", _Table4[Column1], ....
)

might work

[GunnerJ]

@johnt75 thank you for the reply. I'm hoping it's just syntax but I'm not able to run the variable you've given and that I've tried to fill out. The first line says the generate is not of the correct type and then says it can't find rowrank. I'm unsure how to reference a variable table if that's whats needed. I put a link to the file if it would help working with this measure. Thank you again.

Link to the file and picture of errors. 

https://www.dropbox.com/s/hp52obgkpevffs0/OZARKSGO%202.0.pbix?dl=0

[GunnerJ]

@johnt75 if it was necessary to do the calculation earlier in the measure that would be fine. I would guess that it would have to refernce the actual table. The only portion it'd have to come after is Table1 where the table is filterd to dates on or before the vMaxDate.

[johnt75]

If I've correctly understood what you're trying to do in the code, then I think this works

Total Actives6 = 
VAR vMAXDATE = MAX('Date Table'[Date])
VAR __Table0 = 
    CALCULATETABLE(
        GENERATE( 
            VALUES( 'MASTER CHARGE ACTIONS'[BI_ACCT]),
            SELECTCOLUMNS(
                TOPN(1, 'MASTER CHARGE ACTIONS', 'MASTER CHARGE ACTIONS'[ROWRANK]),
                "CHG_DATE", 'MASTER CHARGE ACTIONS'[CHG_DATE],
                "Account Status", 'MASTER CHARGE ACTIONS'[Account Status]
            )
        ),
        ALLEXCEPT('MASTER CHARGE ACTIONS','MASTER CHARGE ACTIONS'[BI_ACCT]),
        'MASTER CHARGE ACTIONS'[CHG_DATE]<=vMAXDATE,
        'MASTER CHARGE ACTIONS'[Account Status] = "Active"
    )
var result = COUNTROWS( __Table0 )
return result

[GunnerJ]

@johnt75 so the ultimate goal is to count "active" accounts when compared to the date filter. The reason I wanted to get the "max" ROWRANK was to focus on the last row. In the example below I have the date as 12/18/2018. The 'Max Acct Date' correctly shows which accounts we should be looking at. Technically there is an active row that is less than the filter date but it's not the latest row. In the example below (i renamed the measure to "totalActive7") it's being marked as active but the ROWRANK 10 is actually inactive. I've really struggled to get that last bit of logic to work. The hope is of the dates that are less than the date filter only look at the max rowrank. If you were able to resolve that piece I'd be incredibly grateful. 

[johnt75 · Accepted solution]

I think this works

Total Actives6 = 
VAR vMAXDATE = MAX('Date Table'[Date])
VAR __Table0 = 
    CALCULATETABLE(
        GENERATE( 
            VALUES( 'MASTER CHARGE ACTIONS'[BI_ACCT]),
            SELECTCOLUMNS(
                TOPN(1, FILTER( 'MASTER CHARGE ACTIONS', 'MASTER CHARGE ACTIONS'[CHG_DATE]<=vMAXDATE),
                    'MASTER CHARGE ACTIONS'[ROWRANK]
                ),
                "CHG_DATE", 'MASTER CHARGE ACTIONS'[CHG_DATE],
                "Account Status", 'MASTER CHARGE ACTIONS'[Account Status]
            )
        ),
        ALLEXCEPT('MASTER CHARGE ACTIONS','MASTER CHARGE ACTIONS'[BI_ACCT])
    )

var result = COUNTROWS( FILTER( __Table0, [Account Status] = "Active" ) )
return result

Its filtering the TOPN for only rows before the max date, then its applying the filter to account status separately.

[GunnerJ]

@johnt75  thank you so much! My testing is showing that works to show if an account is active on a given date.

If I can ask one follow up. When I throw that measure into a card it gives a total I'm not expecting. When I throw the measure in a table next to accounts and filter to is '1' then the number looks to be exactly what I want. I'm unable to update the filter for the card. Do you know why the card might be showing a different value?

[johnt75]

with no filter context on the account number, the card visual is including blank rows in the generated table, and is counting those as well. I haven't been able to figure out how to stop that. 

a workaround might be to have a different measure for the total, which does an ADDCOLUMNS .. SUMMARIZE to get the individual values and sum them up.