Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Say I have the following sample data for multiple audits of projects.
I would like to create a measure for the average of the most recent audits performed to each project. Highlighted in yellow.
It feels like it should be something like
Average from Latest = CALCULATE(AVERAGEA(ProjectAuditsDB[Score]),MAX(ProjectAuditsDB[AuditDate]))
But I am aware that this may try to pick up the last date of them all. Regardless, it is producing an error.
"A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
How can I filter out the last audit date for each project and feed that into the measure?
Many thanks
J
Solved! Go to Solution.
1: Create a Summarize Table
PROJECTS-LASTAUDIT = SUMMARIZE(Audits;Audits[ProjectID];"AVG-LAST AUDIT"; CALCULATE(AVERAGE(Audits[Score]);filter(Audits;Audits[AuditDate]=Max(Audits[AuditDate]))))
2: Create a Measure Average
AVG-LASTAUDIT-PROJECTS = AVERAGE('PROJECTS-LASTAUDIT'[AVG-LAST AUDIT])
Replace with your fields & Table name
The average result is different because you highlighted twice rows of 433PED 8/6 and 14/06
EDIT: If you want all in only a measure
Medida2 =
AVERAGEX (
SUMMARIZE (
Audits;
Audits[ProjectID];
"AVERAGE-LASTAUDIT"; CALCULATE (
AVERAGE ( Audits[Score] );
FILTER ( Audits; Audits[AuditDate] = MAX ( Audits[AuditDate] ) )
)
);
[AVERAGE-LASTAUDIT]
)
@jagostinhoCT go with @Vvelarde solution from the EDIT:
Good job! @Vvelarde
Here you can copy this one has same table name and columns (I think) as yours... Good Luck!
Measure = AVERAGEX ( SUMMARIZE ( ProjectAuditsDB, ProjectAuditsDB[ProjectID], "Average of Last Audit", CALCULATE ( AVERAGE ( ProjectAuditsDB[Score] ), FILTER ( ProjectAuditsDB, ProjectAuditsDB[AuditDate] = MAX ( ProjectAuditsDB[AuditDate] ) ) ) ), [Average of Last Audit] )
Summarize function like his name says create a summary of the table grouping by 1 or several columns and you can use a function to find or calculate something. In the previous measure or table you are grouping all rows by Project ID and calculate the average of every project ID.
Now for add a column with the last date for every project id you should add to the formula:
SummarizeTable=Summarize(Table; GroupField (In your case: ProjectID);Expresion(The average);"Last Date"; LASTDATE(Audits[AuditDate]))
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |