powerbidaxpowerquerycounter

Make a counter column in Power BI based on several columns' values

I have a table called Query 2 in Power BI.

record_id site start_date activity end_date duration total_count
10 1 9/24/2022 baseball 10/1/2022 1 1
10 1 10/11/2022 baseball 11/1/2022 1 2
10 1 9/24/2022 basketball 11/1/2022 3 3
10 1 10/1/2022 basketball 11/1/2022 3
10 1 10/11/2022 basketball 11/1/2022 3
10 1 10/11/2022 football 12/12/2022 2 4
10 1 11/1/2022 football 12/12/2022 2
10 1 11/1/2022 soccer 12/12/2022 1 5

The column total_count is what I'm trying to accomplish, it's not an actual column in the data and will need to be calculated. Its calculated based on the combination of record_id, activity and end_date, When those one of those three columns change, it should count as one. If those values are still the same in the next row, the count should not change and that row remain blank, but if one of the values in the next row changes, the count should increase.I've tried the following code and it's close:

Total_Count =
VAR CurrentRow =
'Query2'[record_id] & "-" & 'Query2'[Activity] & "-" & 'Query2'[end_date] & "-" &   'Query2'[duration]
VAR PreviousRows =
FILTER(
    'Query2',
    'Query2'[record_id] & "-" & 'Query2'[Activity] & "-" & 'Query2'[end_date] & "-" & 'Query2'[duration] < CurrentRow
)
   RETURN
IF(
    RANKX(
        FILTER(
            'Query2',
            'Query2'[record_id] = EARLIER('Query2'[record_id]) &&
            'Query2'[Activity] = EARLIER('Query2'[Activity]) &&
            'Query2'[end_date] = EARLIER('Query2'[end_date]) &&
            'Query2'[duration] = EARLIER('Query2'[duration])
        ),
        'Query2'[start_date],
        ,
        ASC,
        DENSE
    ) = 1,
    COUNTROWS(PreviousRows) + 1,
    BLANK()
)

The count goes wrong after first repeat, where I 4 in my total_count, I get 6.

Solution

  • Try this variation - it counts previous unique combinations instead of rows:

    Total_Count =
  var thisKey = COMBINEVALUES("-", [record_id], [Activity], [end_date], [duration])
  var keyRows = 
    ADDCOLUMNS(
      SUMMARIZE( 'Query2', [record_id], [Activity], [end_date], [duration], [start_date] ),
      "key", COMBINEVALUES("-", [record_id], [Activity], [end_date], [duration])
    )
  var rnk = RANKX(FILTER(keyRows, [key] = thisKey), [start_date], , ASC, DENSE)
  var distinctKeys = SUMMARIZE( FILTER(keyRows, [key] <= thisKey), [key] )
  return IF(rnk = 1, COUNTROWS(distinctKeys), BLANK())