Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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.