Display duplicate row with attempt number

[lwgoBiadmin]

Hi All,

I need to create a table with duplicate values and give them attempt number starting from 1. 

Modal of the Desired table:

Training Module	User name	Attempt No	Questions	user response	correct response	IsCorrect
Lesson1	User1	1	Question1	answer1	answer1	Correct
Lesson1	User1	1	Question2	answer1	answer2	Incorrect
Lesson1	User1	1	Question3	answer3	answer3	Correct
Lesson1	User1	2	Question1	answer3	answer1	Incorrect
Lesson1	User1	2	Question2	answer3	answer2	Incorrect
Lesson1	User1	2	Question3	answer3	answer3	Correct
Lesson1	User1	3	Question1	answer1	answer1	Correct
Lesson1	User1	3	Question2	answer2	answer2	Correct
Lesson1	User1	3	Question3	answer3	answer3	Correct

In above table, training module and user name will be same.
I have found a way to add attempt number using dax.
The problem is the table visual will not show duplicate rows, all the duplicate row will aggregate to single row.

I tried with index then the attempt number won't work. 
DAX:

Attempt Number =

VAR CurrentUser = SELECTEDVALUE('Trainee LP Performance Sub Report'[UserName])

VAR CurrentTrainingModule = SELECTEDVALUE('Trainee LP Performance Sub Report'[TrainingModuleName])

VAR CurrentQuestion = SELECTEDVALUE('Trainee LP Performance Sub Report'[QuestionText])

RETURN

    COUNTROWS(

        FILTER(

            'Trainee LP Performance Sub Report',

            'Trainee LP Performance Sub Report'[UserName] = CurrentUser &&

            'Trainee LP Performance Sub Report'[TrainingModuleName] = CurrentTrainingModule &&

            'Trainee LP Performance Sub Report'[QuestionText] = CurrentQuestion &&

            'Trainee LP Performance Sub Report'[UserResponse] <> ""

        )

    )

Is ther any other formula to get attempt number without removing duplicate value.

Is there any way to acheive this?

Any workaround also welcomed.

Thank you all

[v-yueyunzh-msft · Accepted solution]

Hi , @lwgoBiadmin 

Based on your requirement description, you have managed to create a "attempt number" column with DAX to get the index column for all the attempts within your data model that contains the duplicate data, but when the duplicate data is put into the table visual in the report, the duplicate data will auto disappear and be hidden, which lead to your "attempt number" column display wrong value. Regarding the requirement "Avoid the duplicate data be hidden in your table visual" to make your column "attempt number" display correct value in the table visual also, please kindly check this method to make a try in your environment:

You can create a calculated column like this:

Column = RAND()

Or you can add an index column in Power Query Editor:

Try to place the "Column " into your issue table visual and use Format setting to make the display color of this visual to be white:

Then the duplicate data should not be hidden in your table visual and the column "attempt number" should display correct value in the table visual also. Many thanks!

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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