Live Connection to Azure : Compare two latest rows from the dataset using the gauge chart

[Anonymous]

Hello Everyone,

I am trying to show top 1 by latest date. We have dataset coming from azure stream live connection, it consits of date column and measure value and measurement name. So we want to show latest value ( top 1) for two different values in measurement name. For example:

Raw dataset:

FloorDateShop NamelocationOwner namemeasurement nameSum of measurementvalue

Shopfloor	06/03/19 09:30:58 AM	ABC	XYZ	Myself	Current Count	1984.5
Shopfloor	06/02/19 01:56:28 AM	ABC	XYZ	Myself	Current Count	0
Shopfloor	06/02/19 01:56:28 AM	ABC	XYZ	Myself	Total Count	2359.5
Shopfloor	05/31/19 02:00:38 PM	ABC	XYZ	Myself	Total Count	2359.5
Shopfloor	05/31/19 02:00:33 PM	ABC	XYZ	Myself	Total Count	0
Shopfloor	05/31/19 01:48:14 PM	ABC	XYZ	Myself	Total Count	2359.5
Shopfloor	05/31/19 01:48:09 PM	ABC	XYZ	Myself	Total Count	0
Shopfloor	05/31/19 12:39:42 PM	ABC	XYZ	Myself	Total Count	2284
Shopfloor	05/31/19 12:39:37 PM	ABC	XYZ	Myself	Total Count	804
Shopfloor	05/31/19 12:27:45 PM	ABC	XYZ	Myself	Total Count	2359.5
Shopfloor	05/31/19 12:27:40 PM	ABC	XYZ	Myself	Total Count	538
Shopfloor	05/31/19 12:04:56 PM	ABC	XYZ	Myself	Total Count	2359.5
Shopfloor	05/31/19 12:04:50 PM	ABC	XYZ	Myself	Total Count	1368
Shopfloor	05/31/19 12:04:45 PM	ABC	XYZ	Myself	Total Count	-31
Shopfloor	05/31/19 12:01:41 PM	ABC	XYZ	Myself	Total Count	2359.5
Shopfloor	05/31/19 12:01:36 PM	ABC	XYZ	Myself	Total Count	1368
Shopfloor	05/31/19 12:01:31 PM	ABC	XYZ	Myself	Total Count	2130
Shopfloor	05/31/19 11:58:31 AM	ABC	XYZ	Myself	Total Count	2359.5
Shopfloor	05/31/19 11:58:26 AM	ABC	XYZ	Myself	Total Count	1911
Shopfloor	05/31/19 11:58:21 AM	ABC	XYZ	Myself	Total Count	128.5
Shopfloor	05/31/19 11:54:06 AM	ABC	XYZ	Myself	Total Count	2359.5
Shopfloor	05/31/19 11:54:00 AM	ABC	XYZ	Myself	Total Count	1056
Shopfloor	05/31/19 02:10:42 AM	ABC	XYZ	Myself	Current Count	0
Shopfloor	05/31/19 02:10:42 AM	ABC	XYZ	Myself	Total Count	1124
Shopfloor	05/30/19 10:39:00 PM	ABC	XYZ	Myself	Total Count	1124
Shopfloor	05/30/19 10:38:55 PM	ABC	XYZ	Myself	Total Count	476.1
Shopfloor	05/30/19 10:35:51 PM	ABC	XYZ	Myself	Total Count	1124
Shopfloor	05/30/19 10:35:46 PM	ABC	XYZ	Myself	Total Count	479
Shopfloor	05/30/19 09:37:04 PM	ABC	XYZ	Myself	Total Count	1124
Shopfloor	05/30/19 09:36:58 PM	ABC	XYZ	Myself	Total Count	408.1
Shopfloor	05/30/19 01:40:36 PM	ABC	XYZ	Myself	Current Count	0
Shopfloor	05/30/19 01:40:36 PM	ABC	XYZ	Myself	Current Count	1032

Final Dataset:

Floor	Date	Shop Name	location	Owner name	measurement name	Sum of measurementvalue
Shopfloor	06/03/19 09:30:58 AM	ABC	XYZ	Myself	Current Count	1984.5
Shopfloor	06/02/19 01:56:28 AM	ABC	XYZ	Myself	Total Count	2359.5

Thank you,

Arvind

[v-shex-msft]

HI @Anonymous ,

You can use following measure on visual level filter to choose 'Y' result records.

Tag =
VAR temp =
    CALCULATE (
        MAX ( T3[Date] ),
        ALLSELECTED ( T3 ),
        VALUES ( T3[Floor] ),
        VALUES ( T3[Shop Name] ),
        VALUES ( T3[location] ),
        VALUES ( T3[Owner name] ),
        VALUES ( T3[measurement name] )
    )
RETURN
    IF ( MAX ( T3[Date] ) = temp, "Y", "N" )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin Sheng,

Thank you for your reply. Sadly, I cannot create any measure or calculated columns as I am getting data from azure stream and its live.

Regards

Arvind

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

If you can't create any calculate formulas, it is impossible to use normal slicer/filters achieve your requirement.  

BTW, some of datasource allow you to use measures formula on live connection mode.(SSAS tabular mode, powerbi service) Maybe you can try to get data from power bi service add write formula on it.

Regards,

Xiaoxin Sheng