DAX to add row numbers based on distinct value of one column and sorted by value from another column

[karolp]

Hello together

I prepared a sample data for my case to explain it as precisely as possible.

In my case are following data available:

- DataTime is a column with date/time of offline measurement after EOL. During one measurement is measured multiple measurement features, but all of them are always assigned to the same date/time. Products are not measured in order of production at EOL.

- SerialNumber is a column with unique serial number of every product. It's a combination of date (first 6 digits) and order at EOL (last six digits) during this day, so based on this number it can be recognized which product has been produced earlier and/or later. One product can be measured or once or multiple times (assigned to many DataTime values)

- Feature is a column with name of measured feature during measurement. 

As a remark I can add that initially in raw data of measurement equipment the DataTime column is unique, but after unpivot of all Feature columns is duplicated in many rows.

The input sample table is available under following link: https://drive.google.com/file/d/1oyfUhBwf_hNNmqqEDe0tHTSg72LCWLRM/view?usp=drive_link 

Right now I need to add a row number MeasurementID as a column to the table under following conditions:

- the same row number need to assigned to the same DataTime value

- row number need to be ordered by SerialNumber ascending

- in case if SerialNumber is measured again - assigned to the another DataTime value - the row number then need to be ordered by DataTime ascending.

Below expected example output:

The sample data with expected output with added MeasurementID column according to the described conditions is available under following link: https://drive.google.com/file/d/1WgyPQV-U6Iwuj6mDEQisgPzIj1mukPN-/view?usp=drive_link 

I need the MeasurementID as a base for multiple visualizations on many pages, so I think most appropriate method to calculate it in Power BI is a calculated column or in Power Query. The measure is not sufficient, because as far as I know the filter possibility will be limited/disabled for many visualizations at one time.

Unfortunately I don't know how to write a correct DAX commend to return the MeasurementID as I expect. So I kindly ask you to help me with finding a solution for my problem.

If this will be helpful below I submit the SQL commend which return the MeasurementID according to the above mentioned conditions (sample data output is calculated based on code below). But unfortunately I don't know what will be the DAX equivalent of it.

WITH
cte1 AS (
		SELECT DISTINCT DataTime, 
				SerialNumber
		FROM ALL_Table_unpivot),
cte2 AS (
		SELECT DataTime,
			SerialNumber,
			ROW_NUMBER() OVER(ORDER BY SerialNumber ASC, DataTime ASC) AS MeasurementID
		FROM cte1)
SELECT atu.DataTime,
	atu.SerialNumber,
	atu.Feature,
	cte2.MeasurementID
FROM ALL_Table_unpivot AS atu
LEFT JOIN cte2 ON atu.DataTime = cte2.DataTime
ORDER BY SerialNumber;

If you also consider that different solution - not via DAX- is better applicable as the solution of this problem I would like to hear you proposal as well.

I am grateful to everyone for help in this topic.

[gmsamborn · Accepted solution]

Hi @karolp 

I did it in Power Query with the following steps:

- copy 'ALL_Table_unpivot' to 'ALL_Table_unpivot-index'

- in 'ALL_Table_unpivot-index', remove any columns besides [DataTime] and [SerialNumber]

- select both columns and remove duplicates

- sort by the 2 columns

         - sort by [SerialNumber]

         - while [SerialNumber] is selected, select [DataTime] while pressing CTRL

         - while still pressing CTRL, sort [DataTime]

- add an index (starting at 1)

- starting with 'All_Table_unpivot', merge 'All_Table_unpivot-index'

- expand the ALL-Table_unpivot-index column, and extract [MeasurementID]

I hope this makes sense.

(I added an additional row for SerialNumber '20231003000134' with a different date for testing purposes.)

karolp-2.pbix

[karolp]

Hello @gmsamborn 

thank for the solution!

I checked it and of course it works perfectly as you described with my sample data.

I checked also if I change source file - add new rows with measurement results- and refresh it to recalculate it and it looks still OK.

Thank you again!

[gmsamborn]

@karolp 

I'm glad it works.