Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have this cumulative measure which works ok:
Cumulative Change Notes =
CALCULATE (
COUNTA ( Company_Documents[ID] ),
FILTER (
ALLSELECTED ( Company_Documents ),
Company_Documents[DOC_CREATION_DATE]
<= MAX ( Company_Documents[DOC_CREATION_DATE] )
)
)
However i'd like to limit the result by adding an additional criteria to the filter (starts &&)
Cumulative Change Notes =
CALCULATE (
COUNTA ( Company_Documents[ID] ),
FILTER (
ALLSELECTED ( Company_Documents ),
Company_Documents[DOC_CREATION_DATE]
<= MAX ( Company_Documents[DOC_CREATION_DATE] )
&& Company_Documents[Document Class] = "Change Note"
)
)
I must have the incorrect syntax as there are no records returned despite records being available which match the criteria.
Can anyone give me a pointer on where i've gone wrong when adding the additional filter criteria?
Thanks,
Richard
Solved! Go to Solution.
Hi @RichardJ
Try this.
Cumulative Change Notes =
VAR __maxCreationDate = MAX ( Company_Documents[DOC_CREATION_DATE] )
RETURN
CALCULATE (
COUNTA ( Company_Documents[ID] ),
ALLSELECTED ( Company_Documents ),
Company_Documents[DOC_CREATION_DATE] <= __maxCreationDate,
Company_Documents[Document Class] = "Change Note"
)
Hi @RichardJ
Try this instead, if it does not work consider creating a data sample so we can test and apply formulas.
Hi @RichardJ
Try this.
Cumulative Change Notes =
VAR __maxCreationDate = MAX ( Company_Documents[DOC_CREATION_DATE] )
RETURN
CALCULATE (
COUNTA ( Company_Documents[ID] ),
ALLSELECTED ( Company_Documents ),
Company_Documents[DOC_CREATION_DATE] <= __maxCreationDate,
Company_Documents[Document Class] = "Change Note"
)
@Mariusz Many Thanks for the idea and the quick reply.
This works perfectly.
Initially I didn't think it had worked as I wasn't giving it enough time to calculate (circa 5 seconds) the response.
Thanks again,
Richard
Hi @RichardJ
Try this instead, if it does not work consider creating a data sample so we can test and apply formulas.
@Mariusz - The linked file shows the formulas you provided working. perfectly.
I had to roll back my actual file (not this extract) to get it working properly as it had become corrupted in some way.
Hopefully the attached working example will help someone in future.
Thanks again @Mariusz - getting a solution for this made my day.
Cheers,
Richard
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |