How to Mimic Pivot / Unpivot in a DAX Expression (Measure)?

[BI___guy]

Hi there,

Is it possible to do operations on multiple, specific values in a column, using another column as reference to choose which values? Please see sample data below.

Time	Parameter	Value
12:00 AM	Temp.	20
6:00 AM	Temp.	21
12:00 PM	Temp.	20
6:00 PM	Temp.	19
12:00 AM	Wind Spd.	2.1
6:00 AM	Wind Spd.	2.4
12:00 PM	Wind Spd.	2.3
6:00 PM	Wind Spd.	2.1
12:00 AM	Humidity	0.99
6:00 AM	Humidity	0.99
12:00 PM	Humidity	0.98
6:00 PM	Humidity	0.99

For example, let's say I want a measure to multiply (Temperature) * (Wind Speed) * (Humidity) for each time stamp. Then, putting our measure as the "values" in a PBI matrix visual with Time as the "rows", would be a 2x4 matrix where each time stamp has one value for measure.

Pivot/Unpivot in PQ would work well to re-organize the data such that corresponding T, WS, and H values were all in the same row, but due to the size of the dataset I am working with as well as other constraints, pivot/unpivot is not an option. For similar reasons, I cannot use a summarize table or calculated columns. I must do this with measures. Is it possible?

Many thanks in advance!

[Anonymous · Accepted solution]

HI @BI___guy,

If you are working with largest number of records, I'd like to suggest you create multiple dimension tables with different category fields and use them to design matrix visual.

After these steps, you can write a measure formula to look up original table records to calculate result based on current row and column category values.

Regards,

Xiaoxin Sheng

[wdx223_Daniel]

Measure=SUMX(VALUES(Table[Time]),CALCULATE(PRODUCT(Table[Value])))