Forum Discussion

pehcao's avatar
pehcao
New Member
8 years ago
Solved

Device status report by site

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's avatar
    v-yulgu-msft
    8 years ago

    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

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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.

     

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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