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

 

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

hi Anonymous

 

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)

 

 

 

 

  • 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
      Community 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.