Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Learn from the best! Meet the four finalists headed to the FINALS of the Power BI Dataviz World Championships! Register now
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
LoginDatte | EmpID | Name
2021-02-01 0001 A
2021-02-01 0002 B
2021-02-02 0001 A
2021-02-02 0003 C
I'd like to get this result
LoginDate | Emp_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
The measure can be any other variable name I may use as Yes/No. I can adapt later.
Someone has told me about CROSSJOIN concept but I'm new to DAX. Thank you in advance.
Solved! Go to Solution.
@cpoojan
Create a table named "Usage" from the New Table option as follows:
Add the Fields to a table visual fro the Usage table and create the following measure:
Measure =
IF(
CONTAINS('Log','Log'[EmpID],MAX(Usage[Emp ID]),'Log'[LoginDate],MAX(Usage[LoginDate])),
"Yes",
"No"
)
Result
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
let
M_User = 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, Name = _t]),
Log = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MNI1MFTSUTIwAFOOSrE6mDJGQMoJVcYIpx6ojDGQclaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LoginDate = _t, EmpID = _t, Name = _t]),
Cartesian = let rs=Table.ToRows(M_User) in Table.FromRows(List.TransformMany(List.Distinct(Log[LoginDate]), each rs, (x,y)=>{x}&y), {"Date","Emp ID","Name"}),
#"Nested Join" = Table.NestedJoin(Cartesian, {"Date","Emp ID"}, Log, {"LoginDate","EmpID"}, "Count", JoinKind.LeftOuter),
Count = Table.TransformColumns(#"Nested Join", {"Count", Table.RowCount})
in
Count
| Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@cpoojan
Create a table named "Usage" from the New Table option as follows:
Add the Fields to a table visual fro the Usage table and create the following measure:
Measure =
IF(
CONTAINS('Log','Log'[EmpID],MAX(Usage[Emp ID]),'Log'[LoginDate],MAX(Usage[LoginDate])),
"Yes",
"No"
)
Result
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you so much. Let me try.
Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.
Check out the February 2026 Power BI update to learn about new features.