merging two calculated tables

[jagostinhoCT]

Hello,

I have a source table " ProjectAuditsDB" with values similar to these.

A Project will have more than 1 audit throughout time.

ProjectAuditID	ProjectID	AuditDate	BIM Quality Score	Grade	Audit Age	Audits Status Check
433PPC_BA03	433PPC	13 June 2016	10 %	Amber	20	Recent
433PED_BA07	433PED	14 June 2016	15 %	Red	19	Recent
433SRMA_BA06	433SRMA	08 June 2016	7 %	Amber	25	Recent
B891FMM_BA02	B891FMM	07 June 2016	0 %	Amber	26	Recent
A630BDP_BA10	A630BDP	25 May 2016	9 %	Amber	39	Dated
433GFO_BA01	433GFO	23 May 2016	12 %	Amber	41	Dated
A630POB_BA02	A630POB	16 May 2016	25 %	Amber	48	Dated
433LOLH_BA02	433LOLH	16 May 2016	9 %	Amber	48	Dated
433PCS_BA04	433PCS	11 May 2016	30 %	Green	53	Dated
B879PHM_BA02	B879PHM	11 May 2016	7 %	Amber	53	Dated
433LOM_BA04	433LOM	05 May 2016	0 %	Red	59	Dated

A Summarization table was created using the expression below to allow for the last audits performed to projects.

ProjectAuditsDB_LAST AUDITS SCORE = SUMMARIZE(ProjectAuditsDB,ProjectAuditsDB[ProjectID],"AVG-LAST AUDIT",
CALCULATE(AVERAGE(ProjectAuditsDB[BIM Quality Score]),
filter(ProjectAuditsDB,ProjectAuditsDB[AuditDate]=Max(ProjectAuditsDB[AuditDate]))
),
"Last Date",LASTDATE(ProjectAuditsDB[AuditDate]),
"Project Status",DISTINCT(ProjectsDB[ProjectStatus])

Another Summarization table was created to identify which projects have not been audited at all.

ProjectAuditsDB_NOT AUDITED = SUMMARIZE(Filter(ProjectsDB,COUNTROWS(RELATEDTABLE(ProjectAuditsDB))=0),ProjectsDB[ProjectID],ProjectsDB[ProjectStatus],ProjectsDB[Project Format],ProjectsDB[Office])
)

(many thanks to @Vvelarde with the help for these)

Was trying to create a dashboard which, among other things, would allow me to quickly identify which projects are due on their audit. For such I was thinking on creating a single table with the Last Audits AND the Not Audited data.

How would you say it would be the most correct way of doing it?

Create another summarize table? append these existing 2? any other ideas?

Many thanks for your help.

Joaquim

[Vvelarde · Accepted solution]

hi @jagostinhoCT

Try with this:

UnionTables = Union(SUMMARIZE('ProjectAuditsDB_LAST AUDITS SCORE';'ProjectAuditsDB_LAST AUDITS SCORE'[ProjectID];
'ProjectAuditsDB_LAST AUDITS SCORE'[Project Status];"Status";"Audited");SUMMARIZE('ProjectAuditsDB_NOT

AUDITED';'ProjectAuditsDB_NOT AUDITED'[ProjectID];'ProjectAuditsDB_NOT AUDITED'[ProjectStatus];"Status";"Not Audited")