Counting unique clients by benefit only from their most recent record by date.

[letsgobro]

My goal seems simple but I have sunk a lot of hours into this already and am looking for help with the following:
I am trying to get a distinct count of clients and the different types of benefits they have.  The challenge here is that I only want to know based off the most recent time they were asked.

Here is a sample of a filtered fact table:

In a table visual I'd expect to have it so that we would see

Non cash benefit Id	Count of Clients
7	2
10	2

Benefit IDs 1 and 5 are excluded as their is a more recent record (Assessment_Id) on file for that particular client.

Everything I have tried just doesn't quite get there for one reason or another even after trying to have AI tools help me out. 

I am open to suggestions on how best to figure this out.  I have to stick to DAX measures as this will be in a fabric model.

[gmsamborn · Accepted solution]

Hi @letsgobro 

Would something like this help?

Measure = 
VAR _Client = MAX( 'FactTable'[Client_Id] )
VAR _Date = 
    CALCULATE(
        MAX( 'FactTable'[Date_Id] ),
        ALL( 'FactTable' ),
        'FactTable'[Client_Id] = _Client
    )
VAR _NC_Ben_List =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                'FactTable',
                [Client_Id] = _Client
                    && [Date_Id] = _Date
            ),
            [Non_Cash_Benefit_Id]
        )
    )
VAR _Result =
    CALCULATE(
        DISTINCTCOUNT( 'FactTable'[Client_Id] ),
        FILTER(
            ALL( 'FactTable' ),
            [Non_Cash_Benefit_Id] IN _NC_Ben_List
        )
    )
RETURN
    _Result

Let me know if you have any questions.

letsgobro.pbix

[letsgobro]

Wow, that gets me 99% of the way there.
I was getting a little higher of a count on those benefit types but I altered the VAR _Result to use the ALLSELECTED filter as opposed to ALL. 
After setting that it seems to be spot on.

I kind of understand what each part is doing but I will probably need to research more so I can get a better grasp on how you accomplished this.

Was this the first approach that you thought of or did you try something else first that didn't work?

Thanks a lot!!!!

[gmsamborn]

Hi @letsgobro 

I tried a couple times before I came up with the _NC_Ben_List variable to be able to use IN (in _Result).

I wasn't sure how it would work with real data since it was a pretty small dataset.

Glad it works.