Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

merging two calculated tables

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 Scor...
Need Help
  • Vvelarde's avatar
    Vvelarde
    10 years ago

    hi Anonymous

     

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

  • Vvelarde's avatar
    Vvelarde
    10 years ago

    Anonymous

     

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

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
10 years ago

Hi Anonymous,

 

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

 

ProjectAuditsDB_LAST AUDITS SCORE = SUMMARIZE(ProjectAuditsDB,ProjectAuditsDB[ProjectI​D],"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

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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_BA03433PPC13 June 201610 %6774064Amber21/01/1900 00:00:00Recent
433PED_BA07433PED14 June 201615 %3822731115Red20/01/1900 00:00:00Recent
433SRMA_BA06433SRMA08 June 20167 %34906167Amber26/01/1900 00:00:00Recent
B891FMM_BA02B891FMM07 June 20160 %9429110Amber27/01/1900 00:00:00Recent
A630BDP_BA10A630BDP25 May 20169 %284899107Amber09/02/1900 00:00:00Dated
433GFO_BA01433GFO23 May 201612 %19216664Amber11/02/1900 00:00:00Dated
A630POB_BA02A630POB16 May 201625 %4278050Amber18/02/1900 00:00:00Dated
433LOLH_BA02433LOLH16 May 20169 %351223618Amber18/02/1900 00:00:00Dated
433PCS_BA04433PCS11 May 201630 %01110085Green23/02/1900 00:00:00Dated
B879PHM_BA02B879PHM11 May 20167 %14636796Amber23/02/1900 00:00:00Dated
433LOM_BA04433LOM05 May 20160 %80147177890Red01/03/1900 00:00:00Dated
433PDF_BA03433PDF25 April 201615 %411642939Red11/03/1900 00:00:00Dated
A630BDPO_BA01A630BDPO21 April 20160 %6230556336Red15/03/1900 00:00:00Dated
A630BDPS_BA01A630BDPS21 April 20160 %53074298Amber15/03/1900 00:00:00Dated
A630BDP_BA09A630BDP20 April 201626 %21636399Amber16/03/1900 00:00:00Dated
433PPC_BA02433PPC23 March 20168 %94241138Amber13/04/1900 00:00:00Dated
B891FMM_BA01B891FMM21 March 201680 %0004Green15/04/1900 00:00:00Dated
433BUP_BA10433BUP16 March 201610 %11820328Amber20/04/1900 00:00:00Dated
B796JTL_BA02B796JTL15 March 201626 %1729831273Amber21/04/1900 00:00:00Dated
433PED_BA06433PED08 March 20167 %3357430119Red28/04/1900 00:00:00Dated
433GQCR_BA04433GQCR07 March 201618 %6202414Amber29/04/1900 00:00:00Dated
433SRMA_BA05433SRMA03 March 201615 %1713251Amber03/05/1900 00:00:00Dated
A772GAF_BA01A772GAF01 March 201627 %2743405Amber05/05/1900 00:00:00Dated
A630BDP_BA08A630BDP01 March 20160 %478671181Red05/05/1900 00:00:00Dated
A630BDP_BA08aA630BDP01 March 20163 %242550032Red05/05/1900 00:00:00Dated
A630BDP_BA07A630BDP22 February 20160 %1850084Red13/05/1900 00:00:00Dated
433LOLH_BA01433LOLH22 February 201630 %37639827Amber13/05/1900 00:00:00Dated
433NSG_BA01433NSG15 February 20160 %26286622Amber20/05/1900 00:00:00Dated
A630POB_BA01A630POB12 February 20160 %174311774Amber23/05/1900 00:00:00Dated
B879PHM_BA01B879PHM11 February 201632 %0466151Amber24/05/1900 00:00:00Dated
B465DLP_BA02B465DLP25 January 20160 %     10/06/1900 00:00:00Dated
433PDF_BA02433PDF18 January 20160 %     17/06/1900 00:00:00Dated
433PCS_BA03433PCS11 January 20160 %     24/06/1900 00:00:00Dated
433PPC_BA01433PPC11 January 20160 %     24/06/1900 00:00:00Dated
A630BDP_BA06A630BDP06 January 201624 %57291056Amber29/06/1900 00:00:00Dated
A630TMB_BA06A630TMB05 January 20160 %     30/06/1900 00:00:00Dated
A199BRG_BA02A199BRG04 January 20160 %     01/07/1900 00:00:00Dated
433TWM_BA01433TWM15 June 20160 %88465136Amber19/01/1900 00:00:00Recent
433GQCR_BA05433GQCR20 June 20169 %6195414Red14/01/1900 00:00:00Recent
B740CTC_BA01B740CTC04 July 20160 %78831102Green31/12/1899 00:00:00Recent

 

ProjectsDB

ProjectIDProjectStatusOfficeProject Format
334OCDLiveBristolBIM
433BUPNot LiveManchesterBIM
433FRILiveManchesterBIM
433GFOLiveManchesterBIM
433GQCRLiveManchesterBIM
433LOLHLiveManchesterBIM
433LOMLiveManchesterBIM
433NSGLiveManchesterBIM
433PCSLiveManchesterBIM
433PDFLiveManchesterBIM
433PEDLiveManchesterBIM
433PPCLiveManchesterBIM
433SRMALiveManchesterBIM
433TWMLiveManchesterBIM
A199BRCRLiveLondonBIM
A199BRGLiveManchesterBIM
A325WLOLiveLondonBIM
A569-RSQLiveManchesterBIM
A630BDPLiveManchesterBIM
A630BDPOLiveLondonBIM
A630BDPSLiveLondonBIM
A630POBLiveManchesterBIM
A630TMBNot LiveManchesterBIM
A772GAFLiveManchesterBIM
B465DLPLiveManchesterBIM
B647CPVLiveBristolCAD
B740CTCLiveManchesterBIM
B796JTLLiveManchesterBIM
B879PHMLiveManchesterBIM
B891FMMLiveManchesterBIM

 

 

 

ProjectAuditsDB_LAST AUDITS SCORE

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

433PPC0.1001955034213113/06/2016 00:00:0022RecentLive
433PED0.14662756598240514/06/2016 00:00:0021RecentLive
433SRMA0.073313782991202408/06/2016 00:00:0027RecentLive
B891FMM007/06/2016 00:00:0028RecentLive
A630BDP0.087976539589442825/05/2016 00:00:0041DatedLive
433GFO0.11730205278592423/05/2016 00:00:0043DatedLive
A630POB0.24929130009775216/05/2016 00:00:0050DatedLive
433LOLH0.087976539589442816/05/2016 00:00:0050DatedLive
433PCS0.29569892473118311/05/2016 00:00:0055DatedLive
B879PHM0.073313782991202411/05/2016 00:00:0055DatedLive
433LOM005/05/2016 00:00:0061DatedLive
433PDF0.15395894428152525/04/2016 00:00:0071DatedLive
A630BDPO021/04/2016 00:00:0075DatedLive
A630BDPS021/04/2016 00:00:0075DatedLive
433BUP0.1001955034213116/03/2016 00:00:00111DatedNot Live
B796JTL0.26392961876832815/03/2016 00:00:00112DatedLive
433GQCR0.087976539589442820/06/2016 00:00:0015RecentLive
A772GAF0.26686217008797701/03/2016 00:00:00126DatedLive
433NSG015/02/2016 00:00:00141DatedLive
B465DLP025/01/2016 00:00:00162DatedLive
A630TMB005/01/2016 00:00:00182DatedNot Live
A199BRG004/01/2016 00:00:00183DatedLive
433TWM015/06/2016 00:00:0020RecentLive
B740CTC004/07/2016 00:00:001RecentLive

 

 

ProjectAuditsDB_NOT AUDITED

ProjectIDProjectStatusProject FormatOffice

334OCDLiveBIMBristol
433FRILiveBIMManchester
A569-RSQLiveBIMManchester
A199BRCRLiveBIMLondon
A325WLOLiveBIMLondon
B647CPVLiveCADBristol