Show items with no data in a matrix

[ld17]

I am creating a matrix. My data looks like the below with the user's name and the names of the trainings they have completed. Not all users get assigned all trainings, but when I put this data into a matrix as shown in the second screenshot, it gives the visual appearance that they haven't completed the training. My issue is that I don't think "show items with no data" will work since my data only shows what was assigned to a user, not things that weren't assigned. I would like to use an "if statement" or something similiar so that the cell displays "not assigned" to show that the user didn't have to take that training. 

[DataInsights · Accepted solution]

@ld17,

One solution is to create a calculated table consisting of all combinations of User Full Name and Training Title:

ReportingTable = 
CROSSJOIN ( VALUES ( TrainingTable[User - User Full Name] ), VALUES ( TrainingTable[Training - Training Title] ) )

In this calculated table, create a calculated column:

Completion Date = 
VAR vResult =
    CONVERT (
        LOOKUPVALUE (
            TrainingTable[Completion Date],
            TrainingTable[User - User Full Name], ReportingTable[User - User Full Name],
            TrainingTable[Training - Training Title], ReportingTable[Training - Training Title]
        ),
        STRING
    )
RETURN
    IF ( ISBLANK ( vResult ), "not assigned", vResult )

Create a matrix using the calculated table:

[ld17]

Hello again! Was wondering if I could get one more piece of help on this. I need to be able to distinguish between those who weren't assigned a training and those who are "in progress" or "not started" on a training. So I need some blanks to be transformed into "not assigned," and some need to say "in progress" "or registered." Is there a condition I can add onto the above statement to achieve this?  

[DataInsights]

@ld17,

Would you be able to provide an example of each scenario and the expected result?

[ld17]

Absolutely! My two columns I am trying to reference are highlighted below:

After some tweaks, I have found that this DAX expression will produce the status, but removes the date since the date is in a separate column:

Completion Date = VAR vResult =

    CONVERT (

        LOOKUPVALUE (

            'Site Services Training Matrix'[Transcript - Transcript Status],

            'Site Services Training Matrix'[User - User Full Name], 'Table'[User - User Full Name],

            'Site Services Training Matrix'[Training - Training Title], 'Table'[Training - Training Title]

        ),

        STRING

    )

RETURN

    IF ( ISBLANK ( vResult ), "Not Assigned", vResult)

Gives me this result:

And this, the original I was working with, shows the date and "not assigned," but I cannot figure out how to get it to grab the other statues ("in progress, "in progress / past due," "or registered,") since they are in a different column:

Completion Date = VAR vResult =

    CONVERT (

        LOOKUPVALUE (

            'Site Services Training Matrix'[Transcript - Transcript Completed Date],

            'Site Services Training Matrix'[User - User Full Name], 'Table'[User - User Full Name],

            'Site Services Training Matrix'[Training - Training Title], 'Table'[Training - Training Title]

        ),

        STRING

    )

RETURN

    IF ( ISBLANK ( vResult ), "Not Assigned", vResult)

Gives me this result:

The result I want to achieve would look like the below (dates and statuses combined):

My question is: how do I write a DAX expression to reference both the date from the "transcript-transcript completed date" column (if the user has completed the training) as well as the statues from the "transcript-transcript status" column (if the user is in progress on a training)? Thanks in advance!

[DataInsights · Accepted solution]

@ld17,

Try this:

Completion Date = 
VAR vStatus =
    LOOKUPVALUE (
        'Site Services Training Matrix'[Transcript - Transcript Status],
        'Site Services Training Matrix'[User - User Full Name], 'Table'[User - User Full Name],
        'Site Services Training Matrix'[Training - Training Title], 'Table'[Training - Training Title]
    )
VAR vResult =
    SWITCH (
        TRUE,
        vStatus = "Completed",
            CONVERT (
                LOOKUPVALUE (
                    'Site Services Training Matrix'[Transcript - Transcript Completed Date],
                    'Site Services Training Matrix'[User - User Full Name], 'Table'[User - User Full Name],
                    'Site Services Training Matrix'[Training - Training Title], 'Table'[Training - Training Title]
                ),
                STRING
            ),
        vStatus <> "Completed"
            && NOT ISBLANK ( vStatus ), vStatus,
        "Not Assigned"
    )
RETURN
    vResult

[ld17]

Yes, this works absolutely perfect. Thank you for all of the help in firguring this out. Been sitting on this one for days. Really apprecate it!

[DataInsights]

@ld17,

Glad to hear that works!

[DataInsights · Accepted solution]

@ld17,

One solution is to create a calculated table consisting of all combinations of User Full Name and Training Title:

ReportingTable = 
CROSSJOIN ( VALUES ( TrainingTable[User - User Full Name] ), VALUES ( TrainingTable[Training - Training Title] ) )

In this calculated table, create a calculated column:

Completion Date = 
VAR vResult =
    CONVERT (
        LOOKUPVALUE (
            TrainingTable[Completion Date],
            TrainingTable[User - User Full Name], ReportingTable[User - User Full Name],
            TrainingTable[Training - Training Title], ReportingTable[Training - Training Title]
        ),
        STRING
    )
RETURN
    IF ( ISBLANK ( vResult ), "not assigned", vResult )

Create a matrix using the calculated table:

[ld17]

This works perfect! Thanks SO much!