Forum Discussion
Join several datasets
Hello all, I need some solid advice on this, I have spent some sleepless nights on this and cannot get it to work, all advice is appreciated.
I have several datasets.
User List :
- contains all users
| Name | ID |
Program List:
- contains list of all learning programs and availability status
| Program Name | Created on | Active/Not Active |
Completion List:
- contains list of users that completed some programs
| User Name | Programe Name | Acquired | Date |
What I need is full list of all users, all programs and completion status joined, with date of completion. Completion status being Complete or Incomplete, Date can be blank if incomplete.
| User Name | User Email | User ID | Program Name | Program Completion Status | Date of completion |
| User 1 | Program 1 | Complete | DD-MMM-YYYY | ||
| User 1 | Program 2 | Incomplete | DD-MMM-YYYY | ||
| User 1 | Program 3 | Complete | DD-MMM-YYYY | ||
| User 2 | Program 1 | Complete | DD-MMM-YYYY | ||
| User 2 | Program 2 | Complete | DD-MMM-YYYY | ||
| User 2 | Program 3 | Incomplete | DD-MMM-YYYY | ||
| User 3 | Program 1 | Incomplete | DD-MMM-YYYY | ||
| User 3 | Program 2 | Complete | DD-MMM-YYYY | ||
| User 3 | Program 3 | Incomplete | DD-MMM-YYYY |
Hi, ksab23
I've modeled some data to hopefully meet your expectations.
Measure:
Program Completion Status = IF(SELECTEDVALUE('User Name'[Acquired Date])=BLANK(),"Incomplete","Complete")Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
ksab23 ,
Ensure that there are relationships between the tables. Typically, you would have:
User List[ID] related to Completion List[User ID]
Program List[Program Name] related to Completion List[Program Name]
Create a Full User-Program Matrix:You need to create a table that contains all combinations of users and programs. This can be done using the CROSSJOIN function in DAX.
Go to modelling and create a new table
UserProgramMatrix = CROSSJOIN('User List', 'Program List')Add a new column to the UserProgramMatrix table to determine the completion status and date of completion:Completion Status =
VAR UserID = 'UserProgramMatrix'[ID]
VAR ProgramName = 'UserProgramMatrix'[Program Name]
VAR CompletionRecord =
CALCULATE(
MAX('Completion List'[Acquired Date]),
FILTER(
'Completion List',
'Completion List'[User ID] = UserID &&
'Completion List'[Program Name] = ProgramName
)
)
RETURN
IF(
ISBLANK(CompletionRecord),
"Incomplete",
"Complete"
)Hi, ksab23
I've modeled some data to hopefully meet your expectations.
Measure:
Program Completion Status = IF(SELECTEDVALUE('User Name'[Acquired Date])=BLANK(),"Incomplete","Complete")Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
