distinct users for distinct applications

[KP_PB]

table is like given below

APP NAME  USER ID
APP_A        P1001
APP_A        P1002
APP_A        P1003
APP_A        P1004
APP_B        P1001
APP_B        P1002
APP_B        P1003
APP_C        P1001
APP_C        P1002
APP_C        P1004
APP_D        P1001
APP_D        P1002
APP_D        P1004
APP_D        P1005

need result like, common users from all applications

Sample result

APP NAME   USER ID
APP_A         P1001
APP_A         P1002
APP_B         P1001
APP_B         P1002
APP_C         P1001
APP_C         P1002
APP_D         P1001
APP_D         P1002

[KP_PB · Accepted solution]

Yes, this worked. Thanks a ton!!

[Samarth_18]

Hi @KP_PB ,

You could create a column as below and use it as filter on visual:-

Common_user =
VAR _dist_app =
    DISTINCTCOUNT ( 'Table'[APP N] )
VAR _com_user =
    COUNTROWS ( FILTER ( 'Table', 'Table'[USER ] = EARLIER ( 'Table'[USER ] ) ) )
RETURN
    IF ( _com_user = _dist_app, "Y", "N" )

Output:-

File for reference:-

[balajishriram]

Hi Samarth,

Please, Can you explain how this statement evaluates. Thanks.

FILTER ( 'Table', 'Table'[USER ] = EARLIER ( 'Table'[USER ] )