Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi guys,
I have the feeling this is simple, yet I can't figure it out.
So, I have this table CHANGES (simplified, lol):
Opportunity ID | Change Date | Modified By |
1 | 01-Feb-2020 | John |
1 | 15-Feb-2020 | Adam |
I need:
- the latest Change Date per Opportunity ID;
- who did it (so, 'Modified By' column).
I got, via this measure, the latest Change Date.
The Latest Change Date =
Solved! Go to Solution.
@AliceW ,
For date you can take max(Table[Date])
and for modified by
Modified By Name =
VAR __id = MAX ( 'Table'[Opportunity ID] )
VAR __Change Date = CALCULATE ( MAX( 'Table'[Change Date] ), ALLSELECTED ( 'Table' ), 'Table'[Opportunity ID] = __id )
RETURN CALCULATE ( MAX ( 'Table'[Modified By] ), VALUES ( 'Table'[Opportunity ID] ), 'Table'[Opportunity ID] = __id, 'Table'[Change Date] = __Change Date )
Hi,
I blogged about my solution to a very question yesterday - Show text entries in the value area section of a Pivot Table after meeting certain conditions.
Hope this helps.
Hi @AliceW ,
You can create these 2 measures:
Hi Camargos,
First of all, thank you for replying! The first formula is great, but I get an error for the second: 'a table of multiple values was supplied where a single value was expected'.
Do you know what happened?
Thank yoU!
Ioana
Hi @AliceW ,
It usually happens when you have all set combined set more than 1x, once DISTINCT function hopes only 1 return, if you have more than it, you get an error like u got.
Ricardo
@AliceW ,
For date you can take max(Table[Date])
and for modified by
Modified By Name =
VAR __id = MAX ( 'Table'[Opportunity ID] )
VAR __Change Date = CALCULATE ( MAX( 'Table'[Change Date] ), ALLSELECTED ( 'Table' ), 'Table'[Opportunity ID] = __id )
RETURN CALCULATE ( MAX ( 'Table'[Modified By] ), VALUES ( 'Table'[Opportunity ID] ), 'Table'[Opportunity ID] = __id, 'Table'[Change Date] = __Change Date )
@amitchandakone follow-up question, if I may. I'd like to have this beautiful measure as a column in another table, which has one Opportunity ID per line.
This way, I can use it in visuals as Axis. Super important! Thank you so much.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |