How to use Measure to filter a table based on another table

[jelibier]

Hi, I would like to filter information using a measure not creating another column.

Basically I have two tables (Table 1 – All Users & Table 2 – Users Last Logon(Last 3 months)) Table 1 has no duplicates and Table 2 has duplicates. the only thing these two tables have in common is the Username column. I created a measure to see how many of these users in the past 3 months were active(

Total Active Users =

DISTINCTCOUNT('Table2 - User Last Logon'[User Name]) - CALCULATE(

    DISTINCTCOUNT('Table2 - User Last Logon'[User Name]),

    ISBLANK('Table1 - All Users'[Username])

)

 ). Once I create a table dashboard with Username column & Total Active Users, I see all the username of the users who were active in the past 3 months. I can’t seem to create the opposite of this view. So that I can see all inactive users so I know which users I can remove.

[amitchandak · Accepted solution]

@jelibier , Assume Table 2 and Table 1 joined

active = DISTINCTCOUNT('Table2 - User Last Logon'[User Name])

Inactive = countx(values('Table 1' [User Name]) , if(isblank([Active]), 'Table 1' [User Name], blank()) )

Same logic as

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...