Join several datasets

[ksab23]

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

Email

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

[v-zhangtin-msft · Accepted solution]

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.

[bhanu_gautam]

@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"
)