Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
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] )
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"
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
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] )
Hi Vera !!
I tried but I put a Measure in the wrong place at first. Thank you so much. I appreciate your help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.