topic Re: DAX calculation for factor correction in DAX Commands and Tips

DAX calculation for factor correction

gregers1972 — Thu, 15 Sep 2022 05:18:25 GMT

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

Re: DAX calculation for factor correction

tamerj1 — Thu, 15 Sep 2022 06:45:39 GMT

Hi gregers1972
Please provide an illustrative example

Re: DAX calculation for factor correction

gregers1972 — Thu, 15 Sep 2022 07:33:21 GMT

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

Re: DAX calculation for factor correction

gregers1972 — Thu, 15 Sep 2022 07:35:16 GMT

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

Re: DAX calculation for factor correction

tamerj1 — Thu, 15 Sep 2022 08:04:31 GMT

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

Re: DAX calculation for factor correction

gregers1972 — Thu, 15 Sep 2022 08:11:57 GMT

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

p_i_corrected = (345.3 / 32768) * 500 = 5.27

Re: DAX calculation for factor correction

tamerj1 — Thu, 15 Sep 2022 08:48:03 GMT

gregers1972

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

500 * 'Table'[VarValue] / 32768

Re: DAX calculation for factor correction

gregers1972 — Thu, 15 Sep 2022 09:19:29 GMT

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.

Re: DAX calculation for factor correction

tamerj1 — Thu, 15 Sep 2022 09:26:33 GMT

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?

Re: DAX calculation for factor correction

gregers1972 — Thu, 15 Sep 2022 09:38:03 GMT

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

Re: DAX calculation for factor correction

tamerj1 — Thu, 15 Sep 2022 09:54:40 GMT

gregers1972

You can

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