MAXX function not working properly

Question

I earlier put this question on the SharePoint StackExchange site but it was considered off-topic. Hoping for better luck here. I have a problem with the DAX MAXX function (with a FILTER), used in my Power BI. It works only sometimes and "partially" in very confusing ways. I am following this tutorial: https://docs.microsoft.com/en-us/powerapps/sharepoint-scenario-build-report and the example is given there. I am using this formula:

MaxDaysPending = MAXX(
FILTER('Project Requests', 'Project Requests'[Approved]="Pending"),
'Project Requests'[RequestDateAge]
)

..to a table that looks like this:

The problem is this: In Power BI Desktop:

if I run the formula as-is, I am getting the answer "Blank". (Expected: 316)

If I change "Pending" to "Yes", in the formula, it (correctly) gives the answer 553!

If I change "Pending" to "No", it (wrongly) gives the answer 368! (Expected:458).

Likewise: if I change it to MINX, it works correctly only for "Yes".

I have tried to delete the tables and do it all over again; then it works for a while (even for "Pending"), but then after some data changes in the table or so, it is the same thing again!

Data type of the Approved column is "single line of text". RequestDateAge is a calculated column ( it has its own formula, I omit it here to not clutter this question too much, otherwise it is all described in he given link above) Any ideas? Anybody else noticed some strange behaviour of his formula? This is completely illogical.

A "theory" I have: is the first formula executed before the RequestDateAge formula has finished, some way, so the end result goes wrong? EDIT: No, it's not some race-condition. I tried change RequestDateAge to a non-calculated numeric column (like the Id column), but same behaviour!

It could be extra spaces in your Approved column. A reason you get blank as a result could be your table has Pending with an extra space instead of Pending. I would try to trim your first column of extra spaces. That might make your formula work as expected. — jpf5046
I suspect it's something like @JohnFriel suggested, but if you can post a minimal pbix file that duplicates your problem, I'll take a look. — Alexis Olson
@JohnFriel Thank you for valuable answer! No, there is no blank space what I can see! Data is in a SharePoint list, and no space is added. — Perry J
@Alexis Thank you! That would be great. How can I lend you my pbix file? E-mail? — Perry J
@PerryJ One more thing: you have a Report Level Filter of Status = "Completed". Try removing that filter and see if you get what you expect. — Alexis Olson

Frostytheswimmer Frostytheswimmer · Accepted Answer · 2018-06-20T05:06:42

Check your relationships. Sounds like a bad join.

Alternatively, wrap 'Project Requests' in an ALL.

MaxDaysPending = MAXX(
FILTER(ALL('Project Requests'), 'Project Requests'[Approved]="Pending"),
'Project Requests'[RequestDateAge]
)

MAXX function not working properly

1 Answers