Calculate with 2 filters provides blank as a result

[Anonymous]

Hi, 

I want to get the latest value for a specific time and date, but when I apply two filters to my formula, it results in BLANK.  

I appreciate any tips on that:) . 

This is my calculation: 

Sensor Value latest = CALCULATE (

    fact_sensordata[Sensor Value],

    FILTER (

       ALL ( 'fact_sensordata'[date_id] ),

      'fact_sensordata'[date_id]

            = MAX ( 'fact_sensordata'[date_id] )

   ),

    FILTER (

    ALL ( 'fact_sensordata'[time_id] ),

    'fact_sensordata'[time_id]

   = MAX ( 'fact_sensordata'[time_id] )

   )

)

This is calculation for Sensor Value: 

Sensor Value = AVERAGE(fact_sensordata[sensorValue])

This is my fact table: 

sensor_id	time_id	date_id	Sensor value
1	49	25	11100
1	49	26	1532
1	51	27	48877

[tamerj1]

Hi @Anonymous 

please try

Sensor Value latest =
VAR T1 =
    SUMMARIZE (
        fact_sensordata,
        'fact_sensordata'[date_id],
        'fact_sensordata'[time_id],
        "@SensorValue", [Sensor Value]
    )
VAR T2 =
    TOPN (
        1,
        T1,
        'fact_sensordata'[date_id], DESC,
        'fact_sensordata'[time_id], DESC
    )
RETURN
    MAXX ( T2, [@SensorValue] )

[Jihwan_Kim]

Hi,

I am not sure how your full dataset looks like, but please check the below picture and the attached pbix file.

[goncalogeraldes]

Hello there @Anonymous ! Not sure if this is what you want but try this:

Sensor Value latest =
CALCULATE (
    AVERAGE( fact_sensordata[Sensor Value] ),
    ALL ( 'fact_sensordata'[date_id], 'fact_sensordata'[time_id] ),
    'fact_sensordata'[date_id] = MAX ( 'fact_sensordata'[date_id] )
        && 'fact_sensordata'[time_id] = MAX ( 'fact_sensordata'[time_id] )
)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes