DAX: Create table with LATEST entries

Question

I have a number of sensors in my home, and I want to use PowerBI to display a graph of the temperatures in the different rooms as well as gauge the current/most recent values.

I am having the hardest time writing this in dax:

The data comes to PowerBI from an Azure Table named "DeviceReadings" on the form:

Location (Text, Partition Key)
RowKey (Numeric value based on date stored, not used)
Date (DateTime, TimeStamp)
Temperature (Decimal)
Humidity (Decimal)

What I would like is: (PSEUDOCODE)

Select Location, Last(Date), Temperature, Humidity FROM DeviceReadings 
GROUP BY Location

Expected/Wanted outcome is:

"Mediaroom", "01.10.2017 09:00", 26, 17
"Office", "01.10.2017 09:03", 28, 23
"Livingroom", "01.10.2017 09:13", 22, 32

Obviously, I'm trying to create a calculated DAX table based on these readings. The idea is to create a calculated table that at all times contain the most recent temperature/humidity so that I can display those values in gauge-style visuals.

I have been trying to set table = SUMMARIZECOLUMNS, grouping by Location, and then adding named columns "LastSampled" as "MAX(DeviceReadings[Date]) and then "Temperature";LASTNONBLANK(DeviceReadings[Temperature];DeviceReadings[Temperature]); but that does not get a "connected temperature reading, but something else.

Ideally, I want to group by location, then by max date pr location, and then display the raw temperature + humidity value

I simply want a "Most recent temperature reading" by location displayed on my PowerBI dashboard. (I'm using PowerBI desktop to write all the queries and make the reports, and have not yet uploaded to PowerBI portal)

My DAX skills are fairly low, so I need help in writing the calculated query.

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-10-01T16:24:44

Try using this expression:

Table = SELECTCOLUMNS (
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS (
                GROUPBY (
                    DeviceReadings,
                    DeviceReadings[Location],
                    "LASTDATE", MAXX ( CURRENTGROUP (), DeviceReadings[Date] )
                ),
                "Location", DeviceReadings[Location],
                "Date", [LASTDATE]
            ),
            SELECTCOLUMNS (
                DeviceReadings,
                "DateJoin", DeviceReadings[Date],
                "LocationJoin", DeviceReadings[Location],
                "Humidity", DeviceReadings[Humidity],
                "Temperature", DeviceReadings[Temperature]
            )
        ),
        [Date] = [DateJoin]
            && [Location] = [LocationJoin]
    ),
    "Location", [Location],
    "LastDate", [Date],
    "Temperature", [Temperature],
    "Humidity", [Humidity]
)

I am pretty sure there is an easier way to achieve what you are after but I cannot figure out right now.

Let me know if this helps.

DAX: Create table with LATEST entries

1 Answers