How to get date from 1st table which is not available on 2nd table base on date

[cpoojan]

Hi All BI Expert. 

       I'm working on generating a system usage report. There are 2 table 
1. M_User table is a master table for all user

2. Log table is a report that shows the number of people who log-in to use this system. 

      My challenge is to show people who didn't use the system in Powerbi as Table Visualization.

For example

M_User

Emp ID	Emp_Name
0001	A
0002	B
0003	C

Log

Login	EmpID	Name
2021-02-01	0001	A
2021-02-01	0002	B
2021-02-02	0001	A
2021-02-02	0003	C

My question is how I can get this :

Date	EmpID	Name
2021-02-01	0003	C
2021-02-02	0002	B

I'm stuck with this for a long time (I'm a self-learner and no one in my team can give me a clue ). Thank you all for your advice. 

[Vera_33 · Accepted solution]

Hi @cpoojan 

There are different ways to do it, here is one way, no relationship between M_User table and Log table, use date from Log table, and other two columns from M_User table

Measure = 
VAR T1 =
    ADDCOLUMNS (
        ( CROSSJOIN ( VALUES ( M_User[Emp ID] ), VALUES ( 'Log'[Login] ) ) ),
        "test",
            IF (
                LOOKUPVALUE ( 'Log'[Emp ID], 'Log'[Login], [Login], 'Log'[Emp ID], [Emp ID] )
                    = BLANK (),
                1,
                BLANK ()
            )
    )
RETURN
    MAXX ( T1, [test] )

Or you want a solution in M? Left Anti join should do the trick, to get all the distinct dates from Log table first

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUXJUitUBc4yAHCcYxxjIcVaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emp ID" = _t, Emp_Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp ID", Int64.Type}, {"Emp_Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Distinct( Log[Login])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Emp ID", "Emp_Name", "Custom"}, Log, {"Emp ID", "Name", "Login"}, "Log", JoinKind.LeftAnti)
in
    #"Merged Queries"

[Vera_33 · Accepted solution]

Hi @cpoojan 

There are different ways to do it, here is one way, no relationship between M_User table and Log table, use date from Log table, and other two columns from M_User table

Measure = 
VAR T1 =
    ADDCOLUMNS (
        ( CROSSJOIN ( VALUES ( M_User[Emp ID] ), VALUES ( 'Log'[Login] ) ) ),
        "test",
            IF (
                LOOKUPVALUE ( 'Log'[Emp ID], 'Log'[Login], [Login], 'Log'[Emp ID], [Emp ID] )
                    = BLANK (),
                1,
                BLANK ()
            )
    )
RETURN
    MAXX ( T1, [test] )

Or you want a solution in M? Left Anti join should do the trick, to get all the distinct dates from Log table first

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUXJUitUBc4yAHCcYxxjIcVaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emp ID" = _t, Emp_Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp ID", Int64.Type}, {"Emp_Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Distinct( Log[Login])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Emp ID", "Emp_Name", "Custom"}, Log, {"Emp ID", "Name", "Login"}, "Log", JoinKind.LeftAnti)
in
    #"Merged Queries"

[cpoojan]

Hi Vera !!

Thank you so much for the solution. It works like a magic. 

but if in case that would like to get

LoginEmp       |   ID        | Name |  Measure

2021-02-01       0001         A              1

2021-02-01       0002         B              1

2021-02-01       0003         C               0

2021-02-02       0001         A               1

2021-02-02       0002         B               0

2021-02-02       0003         C               1

[Vera_33 · Accepted solution]

Hi @cpoojan 

Simply modify the measure a little bit

Measure = 
VAR T1 =
    ADDCOLUMNS (
        ( CROSSJOIN ( VALUES ( M_User[Emp ID] ), VALUES ( 'Log'[Login] ) ) ),
        "test",
            IF (
                LOOKUPVALUE ( 'Log'[EmpID], 'Log'[Login], [Login], 'Log'[EmpID], [Emp ID] )
                    = BLANK (),
                0,
                1
            )
    )
RETURN
    MAXX ( T1, [test] )

[cpoojan]

Hi Vera !! 

I tried but I put a Measure in the wrong place at first. Thank you so much. I appreciate your help.