Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
ksab23
Helper I
Helper I

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

NameIDEmail


Program List:
- contains list of all learning programs and availability status

Program NameCreated onActive/Not Active


Completion List:
- contains list of users that completed some programs

User NamePrograme NameAcquiredDate


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 NameUser EmailUser IDProgram NameProgram Completion StatusDate of completion
User 1  Program 1CompleteDD-MMM-YYYY
User 1  Program 2IncompleteDD-MMM-YYYY
User 1  Program 3CompleteDD-MMM-YYYY
User 2  Program 1CompleteDD-MMM-YYYY
User 2  Program 2CompleteDD-MMM-YYYY
User 2  Program 3IncompleteDD-MMM-YYYY
User 3  Program 1IncompleteDD-MMM-YYYY
User 3  Program 2CompleteDD-MMM-YYYY
User 3  Program 3IncompleteDD-MMM-YYYY
1 ACCEPTED SOLUTION
v-zhangtin-msft
Community Support
Community Support

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

vzhangtinmsft_0-1722911492149.pngvzhangtinmsft_1-1722911501299.png

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.

View solution in original post

2 REPLIES 2
v-zhangtin-msft
Community Support
Community Support

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

vzhangtinmsft_0-1722911492149.pngvzhangtinmsft_1-1722911501299.png

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
Super User
Super User

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



Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.