Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |