Device status report by site

[pehcao]

Hello,

I have different sites with unique devices in each site that should report their status daily, but sometimes don't. So i have 2 tables:

Table1: [Device Tag] [Status] [SiteCode] [Date] - Each row is a status report from a device.

Table2: [SiteCode] [SiteName] [DeviceTotal] - Each row is a register of how many devices each site has.

What I need to know is which sites have devices that didn't make a status report on the week before the current one, and how many devices didn't make a status report. So if site A has a [DeviceTotal] of 20, then there should be 20 unique [Device Tag]s reporting their status with site A's [SiteCode] for the week before, so if only 15 [Device Tag]s are found on table1 that did report for that Site Code, then the site A's [SiteName] should pop up on my report, showing how many devices didn't report. I'm a noob at Power Bi so any help would be greatly appreciated.

[v-yulgu-msft · Accepted solution]

Hi @pehcao,

Please first create a calendar table like below, build a one to many relationship to the [Date] column in Table1

Dim Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ),
    "WeekNum", WEEKNUM ( [Date], 2 )
)

Then, create measures:

Count reported device =
CALCULATE (
    COUNT ( Table1[Device Tag] ),
    ALLEXCEPT ( Table2, Table2[Site Code] ),
    FILTER ( 'Dim Date', 'Dim Date'[WeekNum] = WEEKNUM ( TODAY (), 2 ) - 1 )
)

not report =
MAX ( Table2[DeviceTotal] ) - [Count reported device]

diff = IF(MAX(Table2[DeviceTotal])-[Count reported device]=0,0,1)

Best regards,

Yuliana Gu

[Greg_Deckler]

Well, you will need a column in Table1:

c_WeeksAgo = WEEKNUM(TODAY()) - WEEKNUM([Date])

Assuming Table1 and Table2 are related *<-1 Then you could create a measure like:

m_MissingDevicesLastWeek = SUM(Table2[DeviceTotal]) - COUNTROWS(FILTER(RELATED(Table1),[c_WeeksAgo]=1))

Something along those lines. I didn't actually mock up the data for this.