Have measure show same constant value for all rows in a table

[Anonymous]

Hi,

I have a dataset that looks like this:

id	timestamp	temperature	first reading
1	1/1/20 1:00 AM	14	TRUE
1	1/1/20 2:00 PM	12.4	FALSE
1	1/1/20 8:00 AM	16.3	FALSE
1	1/2/20 1:00 AM	14.2	FALSE
1	1/2/20 2:00 PM	15.9	FALSE
1	1/2/20 8:00 AM	14.5	FALSE
1	1/3/20 1:00 AM	13.2	FALSE
1	1/3/20 2:00 PM	19.3	FALSE
1	1/3/20 8:00 AM	16.9	FALSE
2	1/1/20 1:00 AM	14.3	TRUE
2	1/1/20 2:00 PM	12.4	FALSE
2	1/1/20 8:00 AM	16.3	FALSE
2	1/2/20 1:00 AM	14.2	FALSE
2	1/2/20 1:00 PM	15.9	FALSE
2	1/2/20 8:00 AM	14.5	FALSE
2	1/3/20 2:00 AM	13.2	FALSE
2	1/3/20 1:00 PM	13.5	FALSE
2	1/3/20 8:00 AM	16.9	FALSE

Where first reading is a calculated column to find the first temperature value, which looks like this:

first reading = IF(tabledb[timestamp]=CALCULATE(MIN(tabledb[timestamp]),ALLEXCEPT('tabledb', 'tabledb'[id])),True)

I then created a measure to get the first reading value of temperature like this:

First Reading Temperature = CALCULATE(SUM(tabledb[temperature]), tabledb[first reading] = TRUE())

This will only allow me to show first reading temperature on the excact time, is it possible to have it shown on all rows of the table as shown below?

Date	Temperature	first reading temp
1/1/2020	14	14
1/1/2020	12.4	14
1/1/2020	16.3	14
1/2/2020	14.2	14
1/2/2020	15.9	14
1/2/2020	14.5	14
1/3/2020	13.2	14
1/3/2020	19.3	14
1/3/2020	16.9	14

Thanks!

[v-yuta-msft · Accepted solution]

@Anonymous ,

Please modify the measure using dax below:

First Reading Temperature = 
VAR Current_id = MAX('Table'[id])
VAR Current_temp = CALCULATE(MAX('Table'[temperature]), FILTER(ALL('Table'), 'Table'[id] = Current_id && 'Table'[first reading] = TRUE()))
RETURN
IF(MAX('Table'[id]) = Current_id, Current_temp)

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Try

MINX(filter(Table,Table[date]<=EARLIER(Table[date])),Table[Temperature])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601