Complications with using AGGR() and FirstSortedValue()

[vlad_komarov]

Hello all,

I've got a data set that shows multiple devices appearing in several departments (on multiple Sites) over time:

I need to have a count of these devices per Site and per Date, with the condition that these devices will be counted just once per Day, even if they are reported in several departments (or in one department) on one Site several times during that date.

In the sample above: device with ID=00034da8085be should be counted just once at 10:00 a.m. and counts for 02:00 and 04:00 p.m. should be just ignored.

I've tried to build a Measure's formula using AGGR and FirstSortedValue functions, but was not very successful doing so.

I would really appreciate a suggestion how to handle that.

Regards,

Vlad

P.S. the sample file is attached.

[antoniotiman · Accepted solution]

Hi Vladimir,

like this

Count(Aggr(Min(Operational_Hour),Site,Date,DeviceID))

Regards,

Antonio

[vlad_komarov]

Antonio,

Thanks for the suggestion, it works as needed!

Regards,

Vlad