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!Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request 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.
Check out the November 2025 Power BI update to learn about new features.
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
| User | Count |
|---|---|
| 8 | |
| 7 | |
| 5 | |
| 4 | |
| 3 |
| User | Count |
|---|---|
| 20 | |
| 14 | |
| 11 | |
| 9 | |
| 8 |