Forum Discussion
Helper IIShow items with no data in a matrix
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.
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,
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
8 Replies
- DataInsights
Super User
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!
- 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
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!
