Grouping records in table visual

[chadg]

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

Calculation 1 = RUNNINGSUM([Calculation])

Weld Lot = SWITCH(CALCULATE(RIGHT(SUBSTITUTE(SELECTEDVALUE([Calculation 1]),".00",""),1),OFFSET(-1)), "1","2", "2","3", "3","4", "4","5", "5","6", "6","7", "7","8", "8","9", "9","10", "1")

The data in the visual should appear as below:

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

[Ashish_Mathur]

Hi,

Would you be OK with a calculated column formula solution?

[chadg]

@Ashish_Mathur  Calculated columns are fine, thank you.

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

Hi, @chadg 

Based on your information, I create a sample table:

Then create calculated columns:

Calculation = 1

Calculation 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.

[chadg]

@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?