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 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. 

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

     

     

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    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