Need support with calculated columns

[karolp]

Hello together

I have following problem.

I created a working dashboards in Power BI according to the initial assumptions. Right now I need to develop it and here need a support as I am a total beginner with DAX. 

Every dashboard takes data (measurement data) from few measurement equipments. The data which are uploaded from every measurement equipment have the same table format as the columns headers are the same due to the same measurement program in every equipment. Then in PowerBI all tables from diferent measurement equipment are appended into one table ['ALL_Table']. 

To this table I added - actually based totally on solutions from this forum- three calculated columns to calculate:

#1. MeasurementID - index value for every measurement. This value is sorted first by serial product number ['SerialNumber']. The serial product number is corresponding to the order of production (combination of date and produced item number at that day). When the serial product number is duplicated (one product can be measured or once or multiple times) then measurement is sorted by date&time of measurement ['DateTime'], which is a unique value.

It's needed to sort the results in order of production, not according to the time of measurement and also it is a basis for X-axis on line charts to keep the x-axis as continous and avoid the long intervals between the measurement results on the charts.

The code of this calculated column:

MeasurementID = 
VAR Ranks = RANKX(ALL(ALL_Table), ALL_Table[SerialNumber],,ASC)
VAR CountIfExists = CALCULATE(COUNTROWS(ALL_Table),FILTER(ALL_Table,ALL_Table[SerialNumber] = EARLIER(ALL_Table[SerialNumber]) && ALL_Table[DateTime] <= EARLIER(ALL_Table[DateTime]))) - 1
RETURN
    Ranks + CountIfExists

#2. MeasurementNumber- index value for every measurement, but with partition by serial product number and ordered by date&time of measurement ascending.

It's needed to filter only the first measurement of every serial product number.

The code of this calculated column:

MeasurementNumber = 
CALCULATE (
    COUNT ( ALL_Table[SerialNumber] ),
    FILTER (
        ALLEXCEPT ( ALL_Table, ALL_Table[SerialNumber] ),
        ALL_Table[DateTime] <= EARLIER ( ALL_Table[DateTime] )
    )
)

#3.  LastMeasurement - True or False value describing if the measurement is the last measurement (acc to measurement date&time) of every serial product number.

It's needed to filter only the last measurement of every serial product number.

The code of this calculated column:

LastMeasurement = 

IF (
    ALL_Table[DateTime] = CALCULATE( MAX(ALL_Table[DateTime]), FILTER(ALL_Table, ALL_Table[SerialNumber] = EARLIER(ALL_Table[SerialNumber])))
    ,true()
    ,false()
)

Here is the sample output to visualize it:

And this works totally fine.

But as I said I need to develop this dahsboards and so far all measurement features were left in the table as separate columns like it was in the data source files. Unfortunately I need to unpivot all columns with measurement features to have only two columns- one with measurement feature name and second one with measurement result. 

This step unfortunately made those calculated columns #1, #2 and #3 useless as the date&time value is duplicated right now multiple times in the table.

Right now I need to find a solution to restore the functionality of calculated columns in the table with unpivot measurement data. And it has to be calculated columns, not a measure as I need them to filter on all pages and as far as I know the measure can be assigned only as the filter to single visualization. 

Solution scenario I

My first idea was to duplicate the appended table ['ALL_Table'] in status before unpivot. Then remove all not necessary columns (measurement features etc) and leave actually only columns with date&time ['DateTime'] and serial product number ['SerialNumber']. Then I could keep the all three calculated columns with correct values within this table. Then I can create a relationship between both duplicate tables ['DateTime'] of ['ALL_Table'] and ['ALL_Table_unpivot'] and use those columns in my dashboards like before. 

Unfortunately this works correctly only with calculated columns #1 and #3. The columns #2 - MeasurementNumber - after creating a relationship (it does not matter if I change manually the cross filter direction, the outcome is always the same) the calcaulated column starts showing totally wrong values. Sample below:

I have no idea why it is happening only to this calculated column and the two others work properly.

If someone have any idea what should be fixed in the code of calculated colum #2 - MeasurementNumber - to start it work properly also after creating a relationship between those duplicate tables I will truly appreciate your help.

Scenario II

The code of calculated columns #1, #2 and #3 need to be changed accordingly right now in the table after unpivot. 

But here I have no idea how should looks the code of every of it to keep the same functionality as before unpivot.

If you have idea how should look the code of calculated column #1, #2 and #3 with unpivot data (Date&Time duplicates multiple times) to keep the functionality shown by me above I will truly appreciate your help.

Thank you all in advance for your support.

[karolp]

Hello together

A short note from my side to the proposed solution scenario II:

As I said unfortunately I have no idea how should looks the code of every calculated column in DAX to keep the same functionality as before unpivot.

But if my problem description was unclear I submit below how looks working solution for every calculated column #1, #2 and #3 in SQL.

I also attach the table after unpivot with some sample data under following link: https://drive.google.com/file/d/1oyfUhBwf_hNNmqqEDe0tHTSg72LCWLRM/view?usp=share_link . The SQL output below is based also on this sample data table. 

#1. MeasurementID:

WITH
cte AS (
		SELECT DISTINCT DataTime, 
				SerialNumber
		FROM ALL_Table_unpivot)
SELECT DataTime,
		SerialNumber,
		ROW_NUMBER() OVER(ORDER BY SerialNumber ASC, DataTime ASC) AS MeasurementID
FROM cte;

Result (calculated in SQL on partial data, that's why the MeasurementID is lower than on the print screen from original post, but the calculation method of it is the same) :

#2. MeasurementNumber:

WITH
cte AS (
		SELECT DISTINCT DataTime, 
				SerialNumber
		FROM ALL_Table_unpivot)
SELECT DataTime,
		SerialNumber,
		ROW_NUMBER() OVER(PARTITION BY SerialNumber ORDER BY DataTime ASC) AS MeasurementNumber
FROM cte;

Result:

#3. LastMeasurement:

WITH
cte1 AS (
		SELECT DISTINCT DataTime, 
				SerialNumber
		FROM ALL_Table_unpivot),
cte2 AS (
		SELECT MAX(DataTime) as Last_DateTime_Of_SerialNumber,
				SerialNumber
				FROM cte1
				GROUP BY SerialNumber)
SELECT cte1.DataTime,
		cte1.SerialNumber,
		CASE
			WHEN cte1.DataTime IN (cte2.Last_DateTime_Of_SerialNumber) THEN 'True'
			ELSE 'False'
		END AS LastMeasurement
FROM cte1
JOIN cte2 ON cte1.SerialNumber = cte2.SerialNumber;

Result:

If you also consider that different solution 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.