How to calculate row number for transactions within each report using DAX?

[Ruhankarim]

I have three columns in my Power BI dataset: Transaction Start, Report Name, and Status. I am trying to calculate the row number for each transaction within its respective report. However, the current DAX expression I am using seems to be assigning row numbers based on the report name, not the transaction start date.

Here's the DAX expression I am using:

Row Number = ROWNUMBER(ALLSELECTED(Sheet1[Transaction Start],

Sheet1[Report Name],

Sheet1[Status]),

ORDERBY(Sheet1[Transaction Start],DESC),

DEFAULT,

PARTITIONBY(Sheet1[Report Name]))

I also add Transaction Start Column  in Partition By still I am not getting the correct result.

Here's the DAX expression I am using:

Row Number1 = ROWNUMBER(

  ALLSELECTED(Sheet1[Transaction Start],Sheet1[Report Name]),

  ORDERBY(Sheet1[Transaction Start], DESC),

  DEFAULT,

  PARTITIONBY(

    Sheet1[Report Name],

    Sheet1[Transaction Start]

  )

)

Sharing the Snip of result that I am getting below:

Expected results:

• Transaction with Transaction Start date of 11/28/2023 10:34:35 AM should have a row number of 1.
• Transaction with Transaction Start date of 11/28/2023 10:28:06 AM should have a row number of 2.
• Transaction with Transaction Start date of 11/28/2023 6:45:55 AM should have a row number of 3.
• Transaction with Transaction Start date of 11/28/2023 6:32:36 AM should have a row number of 4.
• Transaction with Transaction Start date of 11/27/2023 6:15:10 PM should have a row number of 1.

Actual results:

The row number is the same for all transactions within the same report.

I have tried modifying the DAX expression, but I can't seem to get the desired results.

Any suggestions on how to correctly calculate the row number for transactions within each report using DAX?

Sharing the sample data.

Transaction Start	Report Name	Status
11/28/2023 10:34:35 AM	Report 1	Failed
11/28/2023 10:28:06 AM	Report 1	Failed
11/28/2023 6:45:55 AM	Report 1	Failed
11/28/2023 6:32:36 AM	Report 1	Failed
11/27/2023 6:15:10 PM	Report 1	Failed
11/27/2023 7:06:20 PM	Report 2	Success

[v-xuxinyi-msft · Accepted solution]

Hi , @Ruhankarim 

You can create a calculated column as follows. You can use either of the following two DAXs to achieve this.

Row number =
CALCULATE (
    COUNT ( 'Table'[Report Name] ),
    FILTER (
        'Table',
        'Table'[Transaction Start].[Date]
            = EARLIER ( 'Table'[Transaction Start].[Date] )
            && 'Table'[Report Name] = EARLIER ( 'Table'[Report Name] )
            && 'Table'[Transaction Start] >= EARLIER ( 'Table'[Transaction Start] )
    )
)

Row number1 =
RANKX (
    FILTER (
        'Table',
        [Report Name] = EARLIER ( 'Table'[Report Name] )
            && [Transaction Start].[Date] = EARLIER ( 'Table'[Transaction Start].[Date] )
    ),
    [Transaction Start],
    ,
    DESC
)

Is this the result you expect?

Best Regards,

Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Ruhankarim , Based on what I got, you need the following columns

Transaction Start Date = datevalue([Transaction Start])

Row num = countx(filter(Sheet1, Sheet1[Report Name] = earlier(Sheet1[Report Name])  &&  [Transaction Start Date] = earlier([Transaction Start Date]) && [Transaction Start] <= earlier([Transaction Start]) ), [Transaction Start])

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8