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

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
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]
                )
        )
    )
)

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Many thanks Vvelarde, once again.