Forum Discussion
Solved
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 ...
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.
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"
)
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"
)