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
Best Answer
-
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
Answers
-
Can you please elaborate on when you say does not work? You are getting error, no data, something else?
-
@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
-
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
-
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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!