Forum Discussion
Grouping records in table visual
I have a table visual in a report with a list of work items that are sorted by Job# and User Name. I am trying to create a lotting system for quality control that groups each set of work items into groups of 10.
I created the following dax code in a visual calculation to count to 10 and revert to 1, but it does not check if there are changes in the Job# or User Name column. My current work around is to use a slicer to select one job and user to prevent the numbering from not resetting when it is supposed to. I'm not currently doing anything to prevent each item from moving around other than sorting the visual by the date it was completed. Each item does have a sequential key in the background that could be used for this.
Calculation = 1
| Work Item ID | Job Number | User Name | Calculation | Calculation 1 | Weld Lot |
| 149 | 1234 | John Doe | 1.00 | 1.00 | 1 |
| 682 | 1234 | John Doe | 1.00 | 2.00 | 2 |
| 250 | 1234 | Jack Smith | 1.00 | 3.00 | 1 |
| 879 | 1234 | Jack Smith | 1.00 | 4.00 | 2 |
| 123 | 9876 | John Doe | 1.00 | 5.00 | 1 |
| 456 | 9876 | John Doe | 1.00 | 6.00 | 2 |
| 789 | 5678 | Jane Doe | 1.00 | 7.00 | 1 |
| 891 | 5678 | Jane Doe | 1.00 | 8.00 | 2 |
| 902 | 5678 | Jane Doe | 1.00 | 9.00 | 3 |
| 105 | 5678 | Jane Doe | 1.00 | 10.00 | 4 |
| 114 | 5678 | Jane Doe | 1.00 | 11.00 | 5 |
| 12 | 5678 | Jane Doe | 1.00 | 12.00 | 6 |
| 18 | 5678 | Jane Doe | 1.00 | 13.00 | 7 |
| 4875 | 5678 | Jane Doe | 1.00 | 14.00 | 8 |
| 154 | 5678 | Jane Doe | 1.00 | 15.00 | 9 |
| 627 | 5678 | Jane Doe | 1.00 | 16.00 | 10 |
| 435 | 5678 | Jane Doe | 1.00 | 17.00 | 1 |
| 890 | 5678 | Jane Doe | 1.00 | 18.00 | 2 |
It currently looks like this:
| Work Item ID | Job Number | User Name | Calculation | Calculation 1 | Weld Lot |
| 149 | 1234 | John Doe | 1.00 | 1.00 | 1 |
| 682 | 1234 | John Doe | 1.00 | 2.00 | 2 |
| 250 | 1234 | Jack Smith | 1.00 | 3.00 | 3 |
| 879 | 1234 | Jack Smith | 1.00 | 4.00 | 4 |
| 123 | 9876 | John Doe | 1.00 | 5.00 | 5 |
| 456 | 9876 | John Doe | 1.00 | 6.00 | 6 |
| 789 | 5678 | Jane Doe | 1.00 | 7.00 | 7 |
| 891 | 5678 | Jane Doe | 1.00 | 8.00 | 8 |
| 902 | 5678 | Jane Doe | 1.00 | 9.00 | 9 |
| 105 | 5678 | Jane Doe | 1.00 | 10.00 | 10 |
| 114 | 5678 | Jane Doe | 1.00 | 11.00 | 1 |
| 12 | 5678 | Jane Doe | 1.00 | 12.00 | 2 |
| 18 | 5678 | Jane Doe | 1.00 | 13.00 | 3 |
| 4875 | 5678 | Jane Doe | 1.00 | 14.00 | 4 |
| 154 | 5678 | Jane Doe | 1.00 | 15.00 | 5 |
| 627 | 5678 | Jane Doe | 1.00 | 16.00 | 6 |
| 435 | 5678 | Jane Doe | 1.00 | 17.00 | 7 |
| 890 | 5678 | Jane Doe | 1.00 | 18.00 | 8 |
The Weld Lot counting should reset under 3 conditions: change in Job#, change in user name, or previous weld lot = 10.
I would also like to be able to export the data from the visual into excel, but visual calculations are a preview feature and they do not allow exporting. If there is a way around that, I would appreciate that as well.
Thank you for your help
Hi,
I have used a combination of Power Query and a DAC calculated column formula to solve the problem (though this can be solved in Power Query only as well).
PBI file attached.
Hope this helps.
5 Replies
Hi, chadg
Based on your information, I create a sample table:
Then create calculated columns:
Calculation = 1Calculation 1 = SUMX( FILTER( 'Table', 'Table'[Index] <= EARLIER('Table'[Index]) ), 'Table'[Calculation] )Weld Lot = VAR _Rank = RANKX( FILTER( 'Table', 'Table'[User Name] = EARLIER('Table'[User Name]) ), 'Table'[Index], , ASC, DENSE ) RETURN IF( ISBLANK(_Rank) || MOD(_Rank, 10) = 0, 10, MOD(_Rank,10) )Here is my preview:
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous Thank you for your help.
In your second picture, index 5 and 6 are given lot number 3 and 4. They should be 1 and 2 because the user name is the same, but the job number changed. Can you help with that?
Hi,
Would you be OK with a calculated column formula solution?
Ashish_Mathur Calculated columns are fine, thank you.
Hi,
I have used a combination of Power Query and a DAC calculated column formula to solve the problem (though this can be solved in Power Query only as well).
PBI file attached.
Hope this helps.
