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.
I have a table where I have multiple TSID's with the same value due to adjustments being made to the original TSID which brings in additional transaction lines.
I would like to identify the original transactions as 0, the first adjustment as 1 and the second adjustment as 2.
Each TSID entry has a different created date so I was hoping to use this to be able to identify which transaction is the original, first adjustment and 2nd adjustment in the example below.
created_at_utc | Timesheet_TSID |
07-03-22 21:56 | 710139 |
11-10-21 1:38 | 710139 |
15-10-21 1:18 | 710139 |
So I would like it to look something like this
created_at_utc | Timesheet_TSID | Adjustment |
07-03-22 21:56 | 710139 | 2 |
11-10-21 1:38 | 710139 | 0 |
15-10-21 1:18 | 710139 | 1 |
Solved! Go to Solution.
Hi @Thomas-B-Hudson ,
Try this:
Rank =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= EARLIER ( 'Table'[Date] )
&& 'Table'[ID] = EARLIER ( 'Table'[ID] )
)
) - 1
//-1 to make the first one zero
Proud to be a Super User!
Hi,
Write this calculated column formula
Adjusted = calculate(countrows(Data),filter(Data,Data[created_at_utc]<=earlier(Data[created_at_utc])))-1
Hope this helps.
Hi @Thomas-B-Hudson,
You can create a calculated column that will count the rows that meet a certain criteria (eg <=to a date). Here's a sample:
Rank =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( ALL ( 'Table' ), 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
) - 1
//-1 to make the first one zero
Proud to be a Super User!
Hi @danextian,
This looks to be working in essance, what I am wondering however is I have quite a large dataset which contain these TSID's so you can see from my screenshot below that the values are not 0, 1, 2, etc.
Would you now how to get this formula to reset the count at each new instance of a TSID?
Thanks for your help so far
created_at_utc | Timesheet_TSID | Adjustment | ||
07-03-22 21:56 | 710139 | 364813 | ||
11-10-21 1:38 | 710139 | 308519 | ||
15-10-21 1:18 | 710139 | 308793 |
Hi @Thomas-B-Hudson ,
Try this:
Rank =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= EARLIER ( 'Table'[Date] )
&& 'Table'[ID] = EARLIER ( 'Table'[ID] )
)
) - 1
//-1 to make the first one zero
Proud to be a Super User!
Legend!
I would suggest you to refer this link:
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
I think you will need to index based on each TSID. If this is not what you are looking for please revert back.
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |