Show last value in direct query

[Navaska]

Hi there,

Am currently trying to configure a measure to only show the most current value for a temperature node. to put on a card.

I have the following measure to give me the most recent date for temp-3, 

Latest DateTime = MAXX(FILTER(ALL('temphum'),'temphum'[device_id]="temp-3"),temphum[received_at])

That works fine to get me the most recent date for temp-3, yet when I go to get a sum for the temperature with a filter on that date, the measure just gives me a "(Blank)" response. 

Any help would be appreciated,

Thanks.

Measure =

    CALCULATE (

        SUM ( 'temphum'[temperature] ),

        FILTER (

               ALLSELECTED('temphum'),

               'temphum'[device_id] = "temp-3"&&

               temphum[received_at] = [Latest DateTime]

        )      

    )

 

device_id	received_at	temperature
temp-4	2024-02-28T07:06:41.3582789	22.3
temp-5	2024-02-28T07:08:04.9087494	22.2
temp-3	2024-02-28T07:09:23.3961330	24.3
temp-4	2024-02-28T07:31:45.5189047	22.2
temp-5	2024-02-28T07:33:07.1889647	22
temp-3	2024-02-28T07:34:26.2270203	25.4
temp-1	2024-02-28T07:40:19.3694651	22.3
temp-2	2024-02-28T07:40:33.9704419	22.2
temp-5	2024-02-28T07:43:09.6143039	22
temp-3	2024-02-28T07:44:26.7001187	25.7
temp-4	2024-02-28T07:46:47.5482312	22.1
temp-3	2024-02-28T07:49:28.3065626	26
temp-2	2024-02-28T07:50:36.5310726	22.2

[some_bih]

Hi @Navaska as I understand you need solution at received_at level (not calendar single date level), so it will be the best if you can share pbi file (link to office 365, dropbox, googledrive...) with example for expected output.

[Navaska]

Hi there,

I have given the two links for the dashboard and the data, keep in mind that the data is pulled from an SQL table through Direct Query. If possible, set up an SQL table with that sample data and have power BI direct query to it. That will be the same situation I have. 

Another thought, could it be a timezone configuration error? My data is coming in through AEST (UTC +10), and power Bi is setup to read the data in UTC time? Let me know what you think.

Thanks.

PBI Dashboard https://www.dropbox.com/scl/fi/13v0b3ql2ohg8svff31ri/Dashboard.pbix?rlkey=x3tfc6w4padg3z4qu5jbfe5pe&...

Excel Data https://www.dropbox.com/scl/fi/07p4jtfme4srqtouzs975/test-data.xlsx?rlkey=bdnuyzj0jefxx6o4bz2hpqy3i&...

[some_bih]

Hi @Navaska I will try at first with import mode 

[some_bih]

Hi @Navaska try to create two measures as following (order of creation is important):

1. Max received_at = MAX(Sheet1[received_at])

2. 

Max_temp_at_max_rec =

VAR __device_id=SELECTEDVALUE(Sheet1[device_id])

var __max_received=MAX(Sheet1[received_at])

RETURN

CALCULATE(

sum((Sheet1[temperature])),

FILTER(Sheet1, Sheet1[device_id]=__device_id && Sheet1[received_at]=__max_received)

)

In simple matrix or one the card you should get output as below. 

[Navaska]

Hi there,

I have tried the method you provided, with creating the first measure first, and then creating the second measure second. 

I can see the max_received_at for each device, yet the Max_temp_at_max_rec is still blank. 

When I swap to import mode, the measures work and show the most current value. 

Questions:

• Was the storage mode set to DirectQuery for when you tested those measures? As this is the key factor for having live data from the SQL database
• Does the format of the date time matter e.g. yyyy-mm-ddThh:mm:ss.sssssss

Are there any other settings/data formats I need to configure in order to get this to work? Am struggling to see this working in the way I intended and am worried that it will not be possible.

Thanks.

[some_bih]

Hi @Navaska 

Try v2 (not tested) with IF HASONEVALUE combination. 

I could not test DirectQuery before weekend

Max_temp_at_max_rec v2 =

VAR __device_id=IF ( HASONEVALUE( Sheet1[device_id] ), VALUES( Sheet1[device_id] ))

var __max_received=MAX(Sheet1[received_at])

RETURN

CALCULATE(

sum((Sheet1[temperature])),

FILTER(Sheet1, Sheet1[device_id]=__device_id && Sheet1[received_at]=__max_received)

)

[Navaska · Accepted solution]

Hi there,

Have tried this, and yet it has not worked.

I have another idea with regards to the Top N function for displaying most current temperature. 

I am able to display the most current temperature and humidity.

Thanks for the support.

Cheers

[some_bih]

Hi @Navaska DAX function best practice: ALLSELECTED should be only used in visual as it is "the most complex" function in DAX.

Without your model and details, it is hard to provide solid possible solution, or solution at all, still try v2

Measure v2 =
VAR __LatestDateTime = MAXX(FILTER(ALL('temphum'),'temphum'[device_id]="temp-3"),temphum[received_at])

RETURN
CALCULATE (
SUM ( 'temphum'[temperature] ),
FILTER (
ALLSELECTED('temphum'),
'temphum'[device_id] = "temp-3"&&
temphum[received_at] = __LatestDateTime
)
)

There are some limitations for DirectQury for this function-check link

[Navaska]

Hi there,

I have tried your measure V2 solution, yet still no result, the outcome is (Blank). Is there some advanced option you can enable to see the calculations working behind the measure? 

Is there any other data I can provide to assist you in helping me resolve this issue? 

I have also created a measure that will give an output of 1 or 0 if the most current date of the temp-3 device equals the most current date of the calendar created by the temp-3 device. Is there a way I can use this measure to filter the value shown?

Any help would be appreciated.

Calendar = GENERATESERIES(MIN(temphum[received_at]), MAX(temphum[received_at]), TIME(0, 1, 0))

Check =

VAR DateFilter = MAX('Calendar'[Value])

VAR CurrentDate = CALCULATE(

     MAX(temphum[received_at]),

     ALLEXCEPT(

        'temphum',

        'temphum'[device_id]

     ),

     temphum[received_at] <= DateFilter

)

RETURN IF(LASTNONBLANK(temphum[received_at], 1) = CurrentDate, 1, 0)