DAX calculation for factor correction

[gregers1972]

Good Morning
I have a small issue with some data, where they need to be rescaled as follows. Lets say we have a pressure value p_i (i = 1 - n) and I need to scaled this as : p_i_scaled = (p_i / 32768) * 500, which is pretty straigtforward.

The data are organised row-wise:

Parameters         Date                                Value
A                         2022-09-15 13:22:02       345.3

B                          2022-09-15 13:22:07       255.5

C...                       2022-09-15 13:22:12        37.5

How can I select parameter A's corresponding value for each time point and scale this accordingly.

Any help is greatly appreciated, thanks

Gregers

[gregers1972]

I hope you mean on the data, they are as shown here for 3 series with 5 parameters and values.

VarName	TimeString	VarValue
A	03/05/2022 10:00:03	1395
B	03/05/2022 10:00:03	6470
C	03/05/2022 10:00:03	25
D	03/05/2022 10:00:03	216
E	03/05/2022 10:00:03	15815
A	03/05/2022 10:00:08	1393
B	03/05/2022 10:00:08	6630
C	03/05/2022 10:00:08	0
D	03/05/2022 10:00:08	0
E	03/05/2022 10:00:08	15809
A	03/05/2022 10:00:13	1389
B	03/05/2022 10:00:13	6730
C	03/05/2022 10:00:13	0
D	03/05/2022 10:00:13	0
E	03/05/2022 10:00:13	15806

[gregers1972]

The first datatable was not so easy to read but here again with 3 series and 5 parameters and values.

[tamerj1]

Hi @gregers1972 
I was talking about the expected results. Please illustrate with an example calculation

[gregers1972]

For the first row with p_i = 345.3 the calculation is:

p_i_corrected = (345.3 / 32768) * 500 = 5.27

[tamerj1]

@gregers1972 

Not sure if I correctly understand but you may create a new calculated column 

500 * 'Table'[VarValue] / 32768

[gregers1972]

Thanks and thought the actual calculation would be like this. The trick is that there are several values in multiple rows for the same parameter e.g. A for different timepoints, which must be selected and corrected individually.

[tamerj1]

@gregers1972 
I'm still trying to guess. Please provide the full picture so that I can understand the requirement. How many tables are invloved in this? What are the relationships?

[gregers1972]

It is only a single table.  In the data table shown earlier, there is an "A" row in "VarName" with multiple entries with different "timestring" and each "VarValue" (row wise) I need to correct them differently than e.g. "B"

Select_VarValue = select(VarValue), from Table(VarName(A)) #Maybe need to loop over all A's

Select_VarValue = 500 * 'Table'[Select_VarValue] / 32768

[tamerj1 · Accepted solution]

@gregers1972 

You can 

SUMX ( 'Table', 500 * 'Table'[VarValue] / 32768 )

[tamerj1]

Hi @gregers1972 
Please provide an illustrative example