Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pehcao
New Member

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.

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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)

4.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Employee
Employee

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)

4.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

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.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.