Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have table like this:
ID | Date |
1 | 12/01/2019 |
2 | 14/01/2019 |
2 | 15/01/2019 |
3 | 16/01/2019 |
3 | 17/01/2019 |
I want it to be like this:
ID | Date | Status |
1 | 12/01/2019 | Unresolved |
2 | 14/01/2019 | Unresolved |
2 | 15/01/2019 | Resolved |
3 | 16/01/2019 | Unresolved |
3 | 17/01/2019 | Resolved |
So basically in the alternating format ,I want to add the third column with values: Unresolved, Resolved.
So if a ID occurs first time, it should be "Unresolved", if it is found again then "Resolved".
Solved! Go to Solution.
Hi @sipati ,
Please try the calculated column below.
new column = VAR index = RANKX ( FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ), 'Table'[Date], , ASC ) RETURN IF ( index = 1, "Unsloved", "Resolved" )
Here is the output.
Best Regards,
Cherry
You can do it using earlier or rank
check
Sorry, I am new. If possible, can you share a proper DAX or M query? Thanks.
Hi @sipati ,
Please try the calculated column below.
new column = VAR index = RANKX ( FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ), 'Table'[Date], , ASC ) RETURN IF ( index = 1, "Unsloved", "Resolved" )
Here is the output.
Best Regards,
Cherry
try something like this
CALCULATE(MAX(Table[Date]),FILTER(Data,Table[ID]=EARLIER(Table[ID])&&Table[Date]<EARLIER(Table[Date])))
and use the output to build case statement
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |