Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
How about this?
Classification on Last Date =
IF (
HASONEVALUE ( 'Table'[Client] ),
CALCULATE (
LASTNONBLANK ( 'Table'[Classification], 1 ),
LASTDATE ( 'Table'[Date] )
),
BLANK ()
)
Good Luck!
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!
try with this:
LastClassification = CALCULATE(VALUES(Table2[Classification]),LASTDATE(Table2[Date]))
That will return a multiple values error (even if i add distinct).
Can you have different classification in same day for a client?
Using your sample Data i have this result
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
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])))
Ah the problem was in the "totals" option again, why must that be turned to off for this to work?
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.
Why can i not turn off totals in the table as suggested?
How about this?
Classification on Last Date =
IF (
HASONEVALUE ( 'Table'[Client] ),
CALCULATE (
LASTNONBLANK ( 'Table'[Classification], 1 ),
LASTDATE ( 'Table'[Date] )
),
BLANK ()
)
Good Luck!
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!
"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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |