Solved: merging two calculated tables

jagostinhoCT · ‎07-04-2016

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 · ‎07-05-2016

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

Lima - Peru

View solution in original post

Vvelarde · ‎07-07-2016

@jagostinhoCT

To obtain these results, use this:

ProjectAuditsDB_1ROW_BEFORE LAST AUDITS SCORE =
SUMMARIZE (
    ProjectAuditsDB;
    ProjectAuditsDB[ProjectID];
    "AVG-LAST AUDIT"; CALCULATE (
        AVERAGE ( ProjectAuditsDB[BIM Quality Score] );
        FILTER (
            ProjectAuditsDB;
            ProjectAuditsDB[AuditDate]
                = MINX (
                    TOPN ( 2; ProjectAuditsDB; ProjectAuditsDB[AuditDate] );
                    ProjectAuditsDB[AuditDate]
                )
        )
    )
)

Lima - Peru

View solution in original post

Vvelarde · ‎07-05-2016

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

Lima - Peru

jagostinhoCT · ‎07-05-2016

Fenomenal!

Muchisimas gracias @Vvelarde.

I know I am pushing my luck here but remember that other Summarize table for Last Audits Score?

Is there also way to create a similar summarized table but with the audits before last?

I was trying to create a KPI visual comparing the Last Audits Average Score with the Audits Before Last Audits.

Hello from Manchester, UK, with a slight portuguese accent.

Vvelarde · ‎07-05-2016

In new table Summarize

And Only Change =Max(ProjectAuditsDB[AuditDate])

To <>Max(ProjectAuditsDB[AuditDate])

Lima - Peru

jagostinhoCT · ‎07-06-2016

Hi @Vvelarde

It is not reporting the info from the audits before last.

See image below.

At the top, form all audits list, you can see all of 433PPC project's info.

I have copy pasted the summarized tables to excel, side by side.

On the left side > Last Audits

On the right side > Before Last Audits

The numbers for the Before Last Audits are not correct when compared to the main audits list.

Vvelarde · ‎07-06-2016

hi @jagostinhoCT

The measure Take "all" the audits before last from a Project ID y make an average. (The date and the status is not correct because is the purpose of this measure)

Do you want only the previous to last audit? (1 row)

Lima - Peru

jagostinhoCT · ‎07-07-2016

Hi @Vvelarde

One row per project yes.

Please see image below. Highlighted yellow are most recent audits, produced by the LAST AUDITS summarized table.

Red box would be my desired table, where you would only select one audit per project whose date is the most recent date before last.

I have just selected 3 of the projects to convey the idea. Both summarized tables should list all projects where the filter applies.

Does this make sense?

Vvelarde · ‎07-07-2016

@jagostinhoCT

To obtain these results, use this:

ProjectAuditsDB_1ROW_BEFORE LAST AUDITS SCORE =
SUMMARIZE (
    ProjectAuditsDB;
    ProjectAuditsDB[ProjectID];
    "AVG-LAST AUDIT"; CALCULATE (
        AVERAGE ( ProjectAuditsDB[BIM Quality Score] );
        FILTER (
            ProjectAuditsDB;
            ProjectAuditsDB[AuditDate]
                = MINX (
                    TOPN ( 2; ProjectAuditsDB; ProjectAuditsDB[AuditDate] );
                    ProjectAuditsDB[AuditDate]
                )
        )
    )
)

Lima - Peru

jagostinhoCT · ‎07-07-2016

Many thanks @Vvelarde, once again.

v-qiuyu-msft · ‎07-04-2016

Hi @jagostinhoCT,

In your scenario, does the DAX expression below can really work?

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

In CALCULATE() function, the filter expression can't use aggregate function, so error will throws out when you use this DAX expression. In addition, the sample data you provided it seems that is ProjectsDB dataset, but it doesn't has [ProjectStatus] while DAX need this column.

From the second DAX expression, I find that there is another dataset called ProjectsDB, which data stored in this table?

In your scenario, I would suggest you provide correct ProjectAuditsDB and ProjectsDB dataset data and some screenshots about expected data for our better understanding, so that we cna test it and deliver a solution.

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jagostinhoCT · ‎07-05-2016

Hi @v-qiuyu-msft

Thank you for your reply.

I apologise for the missing datasources. Please see all tables involved pasted below.

Files are also made available here.

The given expressions does work. Resulting table pasted below.

What I am trying to achieve is the creation of a Dashboard that I can use to keep track the Audits coverage and to quickly identify which projects have been audited and the ones that are lacking or dated.

Many thanks for all the help.

ProjectAuditsDB

ProjectAuditIDProjectIDAuditDateBIM Quality ScoreWarnings / 1000 ElementsClashes / 1000 ElementsPurgeable GroupsNBEs / SheetGradeAudit AgeAudits Status Check

433PPC_BA03	433PPC	13 June 2016	10 %	6	774	0	64	Amber	21/01/1900 00:00:00	Recent
433PED_BA07	433PED	14 June 2016	15 %	38	227	31	115	Red	20/01/1900 00:00:00	Recent
433SRMA_BA06	433SRMA	08 June 2016	7 %	3	490	6	167	Amber	26/01/1900 00:00:00	Recent
B891FMM_BA02	B891FMM	07 June 2016	0 %	9	429	1	10	Amber	27/01/1900 00:00:00	Recent
A630BDP_BA10	A630BDP	25 May 2016	9 %	28	489	9	107	Amber	09/02/1900 00:00:00	Dated
433GFO_BA01	433GFO	23 May 2016	12 %	192	166	6	4	Amber	11/02/1900 00:00:00	Dated
A630POB_BA02	A630POB	16 May 2016	25 %	4	278	0	50	Amber	18/02/1900 00:00:00	Dated
433LOLH_BA02	433LOLH	16 May 2016	9 %	35	1223	6	18	Amber	18/02/1900 00:00:00	Dated
433PCS_BA04	433PCS	11 May 2016	30 %	0	1110	0	85	Green	23/02/1900 00:00:00	Dated
B879PHM_BA02	B879PHM	11 May 2016	7 %	14	636	7	96	Amber	23/02/1900 00:00:00	Dated
433LOM_BA04	433LOM	05 May 2016	0 %	80	1471	778	90	Red	01/03/1900 00:00:00	Dated
433PDF_BA03	433PDF	25 April 2016	15 %	4	116	42	939	Red	11/03/1900 00:00:00	Dated
A630BDPO_BA01	A630BDPO	21 April 2016	0 %	62	3055	6	336	Red	15/03/1900 00:00:00	Dated
A630BDPS_BA01	A630BDPS	21 April 2016	0 %	5	307	4	298	Amber	15/03/1900 00:00:00	Dated
A630BDP_BA09	A630BDP	20 April 2016	26 %	21	636	3	99	Amber	16/03/1900 00:00:00	Dated
433PPC_BA02	433PPC	23 March 2016	8 %	9	424	11	38	Amber	13/04/1900 00:00:00	Dated
B891FMM_BA01	B891FMM	21 March 2016	80 %	0	0	0	4	Green	15/04/1900 00:00:00	Dated
433BUP_BA10	433BUP	16 March 2016	10 %	1	182	0	328	Amber	20/04/1900 00:00:00	Dated
B796JTL_BA02	B796JTL	15 March 2016	26 %	17	298	31	273	Amber	21/04/1900 00:00:00	Dated
433PED_BA06	433PED	08 March 2016	7 %	33	574	30	119	Red	28/04/1900 00:00:00	Dated
433GQCR_BA04	433GQCR	07 March 2016	18 %	6	202	4	14	Amber	29/04/1900 00:00:00	Dated
433SRMA_BA05	433SRMA	03 March 2016	15 %	1	713	2	51	Amber	03/05/1900 00:00:00	Dated
A772GAF_BA01	A772GAF	01 March 2016	27 %	274	34	0	5	Amber	05/05/1900 00:00:00	Dated
A630BDP_BA08	A630BDP	01 March 2016	0 %	478	671	1	81	Red	05/05/1900 00:00:00	Dated
A630BDP_BA08a	A630BDP	01 March 2016	3 %	242	550	0	32	Red	05/05/1900 00:00:00	Dated
A630BDP_BA07	A630BDP	22 February 2016	0 %	185	0	0	84	Red	13/05/1900 00:00:00	Dated
433LOLH_BA01	433LOLH	22 February 2016	30 %	376	398	2	7	Amber	13/05/1900 00:00:00	Dated
433NSG_BA01	433NSG	15 February 2016	0 %	26	286	6	22	Amber	20/05/1900 00:00:00	Dated
A630POB_BA01	A630POB	12 February 2016	0 %	17	431	17	74	Amber	23/05/1900 00:00:00	Dated
B879PHM_BA01	B879PHM	11 February 2016	32 %	0	466	1	51	Amber	24/05/1900 00:00:00	Dated
B465DLP_BA02	B465DLP	25 January 2016	0 %						10/06/1900 00:00:00	Dated
433PDF_BA02	433PDF	18 January 2016	0 %						17/06/1900 00:00:00	Dated
433PCS_BA03	433PCS	11 January 2016	0 %						24/06/1900 00:00:00	Dated
433PPC_BA01	433PPC	11 January 2016	0 %						24/06/1900 00:00:00	Dated
A630BDP_BA06	A630BDP	06 January 2016	24 %	57	291	0	56	Amber	29/06/1900 00:00:00	Dated
A630TMB_BA06	A630TMB	05 January 2016	0 %						30/06/1900 00:00:00	Dated
A199BRG_BA02	A199BRG	04 January 2016	0 %						01/07/1900 00:00:00	Dated
433TWM_BA01	433TWM	15 June 2016	0 %	8	846	51	36	Amber	19/01/1900 00:00:00	Recent
433GQCR_BA05	433GQCR	20 June 2016	9 %	6	195	4	14	Red	14/01/1900 00:00:00	Recent
B740CTC_BA01	B740CTC	04 July 2016	0 %	7	883	1	102	Green	31/12/1899 00:00:00	Recent

ProjectsDB

ProjectID	ProjectStatus	Office	Project Format
334OCD	Live	Bristol	BIM
433BUP	Not Live	Manchester	BIM
433FRI	Live	Manchester	BIM
433GFO	Live	Manchester	BIM
433GQCR	Live	Manchester	BIM
433LOLH	Live	Manchester	BIM
433LOM	Live	Manchester	BIM
433NSG	Live	Manchester	BIM
433PCS	Live	Manchester	BIM
433PDF	Live	Manchester	BIM
433PED	Live	Manchester	BIM
433PPC	Live	Manchester	BIM
433SRMA	Live	Manchester	BIM
433TWM	Live	Manchester	BIM
A199BRCR	Live	London	BIM
A199BRG	Live	Manchester	BIM
A325WLO	Live	London	BIM
A569-RSQ	Live	Manchester	BIM
A630BDP	Live	Manchester	BIM
A630BDPO	Live	London	BIM
A630BDPS	Live	London	BIM
A630POB	Live	Manchester	BIM
A630TMB	Not Live	Manchester	BIM
A772GAF	Live	Manchester	BIM
B465DLP	Live	Manchester	BIM
B647CPV	Live	Bristol	CAD
B740CTC	Live	Manchester	BIM
B796JTL	Live	Manchester	BIM
B879PHM	Live	Manchester	BIM
B891FMM	Live	Manchester	BIM

ProjectAuditsDB_LAST AUDITS SCORE

ProjectIDAVG-LAST AUDITLast DateAudit Age (LastAudits)Audits Status Check (Last Audits)Project Status

433PPC	0.10019550342131	13/06/2016 00:00:00	22	Recent	Live
433PED	0.146627565982405	14/06/2016 00:00:00	21	Recent	Live
433SRMA	0.0733137829912024	08/06/2016 00:00:00	27	Recent	Live
B891FMM	0	07/06/2016 00:00:00	28	Recent	Live
A630BDP	0.0879765395894428	25/05/2016 00:00:00	41	Dated	Live
433GFO	0.117302052785924	23/05/2016 00:00:00	43	Dated	Live
A630POB	0.249291300097752	16/05/2016 00:00:00	50	Dated	Live
433LOLH	0.0879765395894428	16/05/2016 00:00:00	50	Dated	Live
433PCS	0.295698924731183	11/05/2016 00:00:00	55	Dated	Live
B879PHM	0.0733137829912024	11/05/2016 00:00:00	55	Dated	Live
433LOM	0	05/05/2016 00:00:00	61	Dated	Live
433PDF	0.153958944281525	25/04/2016 00:00:00	71	Dated	Live
A630BDPO	0	21/04/2016 00:00:00	75	Dated	Live
A630BDPS	0	21/04/2016 00:00:00	75	Dated	Live
433BUP	0.10019550342131	16/03/2016 00:00:00	111	Dated	Not Live
B796JTL	0.263929618768328	15/03/2016 00:00:00	112	Dated	Live
433GQCR	0.0879765395894428	20/06/2016 00:00:00	15	Recent	Live
A772GAF	0.266862170087977	01/03/2016 00:00:00	126	Dated	Live
433NSG	0	15/02/2016 00:00:00	141	Dated	Live
B465DLP	0	25/01/2016 00:00:00	162	Dated	Live
A630TMB	0	05/01/2016 00:00:00	182	Dated	Not Live
A199BRG	0	04/01/2016 00:00:00	183	Dated	Live
433TWM	0	15/06/2016 00:00:00	20	Recent	Live
B740CTC	0	04/07/2016 00:00:00	1	Recent	Live

ProjectAuditsDB_NOT AUDITED

ProjectIDProjectStatusProject FormatOffice

334OCD	Live	BIM	Bristol
433FRI	Live	BIM	Manchester
A569-RSQ	Live	BIM	Manchester
A199BRCR	Live	BIM	London
A325WLO	Live	BIM	London
B647CPV	Live	CAD	Bristol

merging two calculated tables

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly