Forum Discussion

cpoojan's avatar
cpoojan
Regular Visitor
5 years ago
Solved

How to return value from CROSSJOIN

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 peopl...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    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

     

     

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago
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