Trouble shooting a INDEX/MAX/Collect formula with an IF statement

So this is the weirdest thing - at least for me. The same formula works great for one column, but not for the other.

It works fantastic here: =IF(COUNTIFS({DAX Ambient Inventory Range Install Date}, ISDATE(@cell), {DAX Ambient Inventory Project #}, [Project Number]@row) > 0, INDEX(MAX(COLLECT({DAX Ambient Inventory Range Install Date}, {DAX Ambient Inventory Range Install Date}, ISDATE(@cell), {DAX Ambient Inventory Project #}, [Project Number]@row)), 1))

But when I use the same formula here, it only provides data for the projects, that have no project number.

=IF(COUNTIFS({DAX iOS iPod Touch Inventory Ship Date}, ISDATE(@cell), {DAX iOS iPod Touch Inventory Project Number}, [Project Number]@row) > 0, INDEX(MAX(COLLECT({DAX iOS iPod Touch Inventory Ship Date}, {DAX iOS iPod Touch Inventory Ship Date}, ISDATE(@cell), {DAX iOS iPod Touch Inventory Project Number}, [Project Number]@row)), 1))

What could be the issue? Any suggestions on the steps to trouble shoot?

Thanks so much for sharing your advice.

Best regards, Barbara

Find more posts tagged with

Accepted answers

Kelly Moore

Hey @Barbara Witt

That is strange. It doesn't make sense.

You could delete all your cross sheet references and build them again, to be sure there isn't an inadvertent error. I would do this regardless. Start from scratch. Especially the range that references your project numbers since that seems to be the problem- start there.

You could add in a known project number "xxx1234" instead of using the [Project Number]@row to see if it can find that specific instance.

You could see if you could force the formula to avoid blank project names by adding additional criteria to your existing formula: {DAX iOS iPod Touch Inventory Project Number},@cell<>""

SK will probably be able to offer additional help.

-Kelly

All comments

Sameer Karkhanis

Can you please elaborate on when you say does not work? You are getting error, no data, something else?

Barbara Witt

@SK It's complicated, I know....

The first formula returns the MAX date for the projects with a project number. The second formula returns a date for the projects without a project number.

These are the same formulas, thus I would think they should behave the same...It's strange.

Thanks for your help. Best regards, Barbara

Kelly Moore

Hey @Barbara Witt

That is strange. It doesn't make sense.

You could add in a known project number "xxx1234" instead of using the [Project Number]@row to see if it can find that specific instance.

You could see if you could force the formula to avoid blank project names by adding additional criteria to your existing formula: {DAX iOS iPod Touch Inventory Project Number},@cell<>""

SK will probably be able to offer additional help.

-Kelly

Barbara Witt

Hello @KDM and @SK

Still strange, but that fixed it, Kelly. I added the additional criteria of making sure the Project Number is not empty. I only added it to the COLLECT portion of the formula, not the IF portion of the formula.

Still don't understand why I don't have to do it with the other scenario, but that is ok. It's working now and that is the most important.

As always, thank so much for your assistance.

=IF(COUNTIFS({DAX iOS iPhone Inventory Ship Date}, ISDATE(@cell), {DAX iOS iPhone Inventory Project Number}, [Project Number]@row)>0, INDEX(MAX(COLLECT(({DAX iOS iPhone Inventory Ship Date}, {DAX iOS iPhone Inventory Ship Date}, ISDATE(@cell), {DAX iOS iPhone Inventory Project Number}, [Project Number]@row, {DAX iOS iPhone Inventory Project Number}, @cell<>"")),1))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.