Forum Discussion
Group by / Summarize with 'incomplete' data?
Hi Power BI Community,
I've got an interesting problem that perhaps you can shed some light on.
Please consider the below two tables:
| DocumentID | Document |
| 1 | DocA |
| 2 | DocB |
| 3 | DocC |
| PersonID | DocumentID | Date_Added |
| 1 | 1 | 01/01/2018 |
| 1 | 2 | 26/02/2018 |
| 2 | 1 | 13/03/2018 |
| 3 | 3 | 21/09/2018 |
What I'm looking for is all DocumentIDs to be returned with either the Date_Added or blank per PersonID. For example:
| PersonID | DocumentID | Date_Added |
| 1 | 1 | 01/01/2018 |
| 1 | 2 | 26/02/2018 |
| 1 | 3 | |
| 2 | 1 | 13/03/2018 |
| 2 | 2 | |
| 2 | 3 | |
| 3 | 1 | |
| 3 | 2 | |
| 3 | 3 | 21/09/2018 |
Cheers,
cmob
Anonymous , Try a new table
ADDCOLUMNS(crossjoin(person, selectcolumns(document,"ID",[document ID],"Document",[Document])), "Date_Added new", if([ID] = [document ID], [Date_Added], blank()))
2 Replies
Anonymous , Try a new table
ADDCOLUMNS(crossjoin(person, selectcolumns(document,"ID",[document ID],"Document",[Document])), "Date_Added new", if([ID] = [document ID], [Date_Added], blank()))
Phenomonal reply speed as always, Amit. I tweaked the syntax a bit and then created a summary table to flatten the data as it returned some duplicates. The end result is exactly what I was after.
For future reference the DAX used was :
xJoin_Document =
ADDCOLUMNS (
CROSSJOIN (
Child_Documents,
SELECTCOLUMNS (
Child_Document_Types,
"ID1", [DocumentTypeID],
"Document", ( Child_Document_Types[Type] )
)
),
"Date_Added new", IF ( [ID1] = [DocumentTypeID], [Date_Added], BLANK () )
)I then flattened it with:
Flat_Document =
SUMMARIZE (
xJoin_Document,
xJoin_Document[ID1],
xJoin_Document[ChildID],
"Date Added", MAX ( xJoin_Document[Date_Added new] )
)I'll combine the two to optimise the model further.
Cheers,cmob