Calculations including rows with no data

Question

Hello&nbsp;&nbsp;I am working on a piece of work to identify&nbsp;students who are not engaging in remote learning. I am using the Microsoft Graph API's to pull usage reports from Microsoft 365. I would like to get day by day active usage of the service and more importantly show students that have now been active.&nbsp;I have 2 tables the first table is an export from the School records systems and the other is the report from the graph. I have been able to match users based on the unique id which is synced for all users but im unable to get the calculations to work as i need them.&nbsp;&nbsp;Below is how my data looks&nbsp;Table 1 (Student data system report)NameIDUser 11234User 21235User 31236User 41237User 51238User 61239&nbsp;Table 2 (Microsoft Graph usage Report)UPNIDReportDateActive DateUser1@example.com123405/01/202105/01/2021User2@example.com123505/01/202121/12/2020User4@example.com123705/01/202105/01/2021User6@example.com123905/01/202104/01/2021&nbsp;The issue im having is that now all students are showing in the usage report which i assume is due to them never being active. I need to record these students as not active. Below is the result i am trying to achieve.&nbsp;&nbsp;NameIDUPNActiveNot ActiveActive FilterUser 11234User1@example.com1&nbsp;ActiveUser 21235User2@example.com&nbsp;1Not ActiveUser 31236&nbsp;&nbsp;1Not ActiveUser 41237User4@example.com1&nbsp;ActiveUser 51238&nbsp;&nbsp;1Not ActiveUser 61239User6@example.com&nbsp;1Not Active&nbsp;&nbsp;&nbsp;24&nbsp;&nbsp;I am able to acheive the active count using the formular below, Which i can also tweak to get the count for non active students that exist in Table 2, however i can't get this to work when i unhide blank rows.&nbsp;&nbsp;Active&nbsp;&nbsp;&nbsp;Active = IF(Table2[TeamsActiveDate] = Table2[ReportDate],1,BLANK())&nbsp;&nbsp;&nbsp;Not Active&nbsp;&nbsp;Active = IF(TAble2[TeamsActiveDate] &lt;&gt; Table2[ReportDate],1,BLANK())&nbsp;&nbsp;&nbsp;Any help on this would be amazing

v-alq-msft · Answer

Hi,&nbsp;AJ42&nbsp;
&nbsp;
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table 1:

Table 2:

&nbsp;
There is a relationship between two tables. You may create measures as below.
Active = 
var tab = 
SUMMARIZE(
    'Table 1',
    'Table 1'[Name],
    'Table 1'[ID],
    "Result",
    IF(
        MAX('Table 2'[ReportDate])=MAX('Table 2'[Active Date])&amp;&amp;MAX('Table 2'[UPN])&lt;&gt;"",
        1
    )
)
return
SUMX(
    tab,
    [Result]
)Inactive = 
var tab = 
SUMMARIZE(
    'Table 1',
    'Table 1'[Name],
    'Table 1'[ID],
    "Result",
    IF(
        MAX('Table 2'[ReportDate])&lt;&gt;MAX('Table 2'[Active Date])||MAX('Table 2'[UPN])="",
        1
    )
)
return
SUMX(
    tab,
    [Result]
)Active Filter = 
var tab = 
SUMMARIZE(
    'Table 1',
    'Table 1'[Name],
    'Table 1'[ID],
    "Result",
    IF(
        MAX('Table 2'[ReportDate])=MAX('Table 2'[Active Date])&amp;&amp;MAX('Table 2'[UPN])&lt;&gt;"",
        "Active",
        "Inactive"
    )
)
return
IF(
    ISFILTERED('Table 1'[Name]),
    MAXX(
        tab,
        [Result]
    )
)
&nbsp;
Result:

&nbsp;
Best Regards
Allan
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

amitchandak · Answer

AJ42 , see my HR blog with two date you can alwaus get active list of user on a date 
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

aj42 · Answer

amitchandak,&nbsp;Thanks for getting back to me so promptly. I think the main issue i have is i have more users in Table1 than i do in Table2. I have enebled show items with no data but i need to be able to record those as not active. Do you know if it is possible to include calculations for blank cells?&nbsp;

pc2790 · Answer

Hi,&nbsp;I tried implementing using the data you provided and it can be done using M query. The code is as below:&nbsp;let
    MergedTable_ = Table.NestedJoin(UserTable, {"ID"}, UsageTable, {"ID"}, "UsageTable", JoinKind.LeftOuter),
    #"Expanded UsageTable" = Table.ExpandTableColumn(MergedTable_, "UsageTable", {"UPN"}, {"UsageTable.UPN"}),
    addedUsage_= List.Difference(UserTable[ID], UsageTable[ID]),
     T1_ = Table.AddColumn(Table.SelectRows(UserTable, each List.Contains(addedUsage_, [ID])), "Active Filter", each "Not active", type text),
     T2_ = Table.AddColumn(Table.SelectRows(#"Expanded UsageTable", each not List.Contains(addedUsage_, [ID])), "Active Filter", each "Active", type text),
     FinalList = Table.Combine({T1_,T2_})
in
    FinalListThe result looks something like this:&nbsp;&nbsp;The other two columns for Active and Inactive cna be easily added based on ACtive filter column.Let me know if you want me to do it.I hope this solves your purpose&nbsp;

aj42 · Answer

HI&nbsp;PC2790&nbsp;&nbsp;Thanks you so much that looks like what i am looking for. Do you have details on how to apply this? Is this a new table or a measure? Not done anything with M query before.&nbsp;Thanks

Forum Discussion

Calculations including rows with no data

10 Replies

Related content

Row wise calculation

DAX Measure for Ranking the valid rows and calculating the average rating

Matrix - Can Primary Row Be It's Own Calculation From Sub-Rows?

Create calculated row

How to make a DAX calculation return BLANK rows

Name	ID
User 1	1234
User 2	1235
User 3	1236
User 4	1237
User 5	1238
User 6	1239

UPN	ID	ReportDate	Active Date
[email protected]	1234	05/01/2021	05/01/2021
[email protected]	1235	05/01/2021	21/12/2020
[email protected]	1237	05/01/2021	05/01/2021
[email protected]	1239	05/01/2021	04/01/2021

Name	ID	UPN	Active	Not Active	Active Filter
User 1	1234	[email protected]	1		Active
User 2	1235	[email protected]		1	Not Active
User 3	1236			1	Not Active
User 4	1237	[email protected]	1		Active
User 5	1238			1	Not Active
User 6	1239	[email protected]		1	Not Active
			2	4