Trying to calculate most recent classification

[Casteless]

Suppose there are three columns:

CLIENT | Classification | Date

1          |   A                  | 1/1/2016

1          |   A                  | 1/1/2016

1          |   B                  | 2/1/2016

1          |  C                  | 3/1/2016

1          |  A                  | 4/1/2016

2          |   B                | 1/1/2016

2          |   B                  | 2/1/2016

2          |  A                  | 3/1/2016

2          |   A                 | 3/1/2016

2          |  C                  | 4/1/2016

How would I grab the most recent Classification for each client to output:

Client | Classification

1        | A

2        | C

I've tried multiple things but everything always returns all combinations e.g.

1        | A

1        | B

1        | C

2        | A

2        | B

2        | C

Any help?

[Vvelarde]

@Casteless

Hi, please watch the video and let me know if helps you-

[Casteless]

Hi Vvelarde, 

That won't work as the rest of the values in the table require aggregation across a range of dates.

As such the |Date| column can't be  a part of the visual

Thanks for the attempt!

[Vvelarde]

@Casteless

try with this:

LastClassification = CALCULATE(VALUES(Table2[Classification]),LASTDATE(Table2[Date]))

[Casteless]

That will return a multiple values error (even if i add distinct). 

[Vvelarde]

@Casteless

Can you have different classification in same day for a client?

Using your sample Data i have this result

[Casteless]

Well this is odd, are you sure you copied in the duplicate dates?

I am also using the sample data and I recieve the multiple values error.

the measure:

class2 = CALCULATE(VALUES(Testtable[class]),LASTDATE(Testtable[date]))

the data: Testtable

[Vvelarde]

@Casteless

To solve the error you can put the Table's Total OFF.

The error appears because in totals, you have 2 or more rows with last date (Example: 4/1/16). If you don't want to switch off the totals you need to add to dax formula:

 Class2 = IF(HasOneValue(Testtable[client]),CALCULATE(VALUES(Testtable[class]),LASTDATE(Testtable[date])))

[Casteless]

Ah the problem was in the "totals" option again, why must that be turned to off for this to work?

[Casteless]

For some reason when i try to use this solution with my real data the "General" section of format is not available? so i can't turn off totals.

[Casteless]

Why can i not turn off totals in the table as suggested?

[Vvelarde]

@Casteless

Try to Delete the measure of the visual and add again.

Regards,

[Sean · Accepted solution]

@Casteless

How about this?

Classification on Last Date =
IF (
    HASONEVALUE ( 'Table'[Client] ),
    CALCULATE (
        LASTNONBLANK ( 'Table'[Classification], 1 ),
        LASTDATE ( 'Table'[Date] )
    ),
    BLANK ()
)

Good Luck!

[Casteless]

Sean,

That works perfectly!

I tried to use LastnonBlank in my original attempts but was lacking an expression.

I was unaware you could put "1" for an expression, what does that mean?

VVlarde thanks for your help as well!

[Sean]

@Casteless

"placing 1 as the second parameter has the same effect as just ignoring this parameter"

You can read more about it here...

http://exceleratorbi.com.au/lastnonblank-explained/