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.

 

ProjectAuditIDProjectIDAuditDateBIM Quality ScoreGradeAudit AgeAudits Status Check
433PPC_BA03433PPC13 June 201610 %Amber20Recent
433PED_BA07433PED14 June 201615 %Red19Recent
433SRMA_BA06433SRMA08 June 20167 %Amber25Recent
B891FMM_BA02B891FMM07 June 20160 %Amber26Recent
A630BDP_BA10A630BDP25 May 20169 %Amber39Dated
433GFO_BA01433GFO23 May 201612 %Amber41Dated
A630POB_BA02A630POB16 May 201625 %Amber48Dated
433LOLH_BA02433LOLH16 May 20169 %Amber48Dated
433PCS_BA04433PCS11 May 201630 %Green53Dated
B879PHM_BA02B879PHM11 May 20167 %Amber53Dated
433LOM_BA04433LOM05 May 20160 %Red59Dated

 

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

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

10 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    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")

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      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's avatar
        Vvelarde
        Community Champion
        10 years ago

        In  new table Summarize

         

        And Only Change =Max(ProjectAuditsDB[AuditDate])

         

        To  <>Max(ProjectAuditsDB[AuditDate])

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community 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