cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cpoojan
New Member

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

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 IDEmp_Name

0001

A
0002B
0003C

 

Log

LoginEmpIDName
2021-02-010001A
2021-02-010002B
2021-02-020001A
2021-02-020003C

 

My question is how I can get this :

 

Date EmpIDName
2021-02-010003C
2021-02-020002B

 

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. 

1 ACCEPTED SOLUTION
Vera_33
Solution Sage
Solution Sage

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] )

 

 

Vera_33_0-1617345720620.png

 

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_2-1617346120724.png

 

Vera_33_1-1617346108223.png

 

View solution in original post

1 REPLY 1
Vera_33
Solution Sage
Solution Sage

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] )

 

 

Vera_33_0-1617345720620.png

 

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_2-1617346120724.png

 

Vera_33_1-1617346108223.png

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors