install(EXPORT ... EXPORT_PACKAGE_DEPENDENCIES) forces export file to be the <package>-config.cmake file
The new EXPORT_PACKAGE_DEPENDENCIES keyword for install(EXPORT) writes find_dependency() calls into the generated export file. This assumes that the export file is the <package>-config.cmake file for the package, because the find_dependency() call works by forcing the calling scope to return if the dependency cannot be found. That works if the find_dependency() call is in the <package>-config.cmake file, but it doesn't work if it is in a file included by <package>-config.cmake.
Some projects will need to define their own custom <package>-config.cmake file to account for package-specific logic like defining functions, perhaps including multiple export files to define the package targets, and so on. We cannot rely on the export file always being the <package>-config.cmake file. However, the generated export files don't provide a way to say to whatever included them "I was successful" or "I failed to find one or more dependencies". Up to CMake 3.28, a custom <package>-config.cmake file would always assume the generated export files it includes were successful.
Thinking more about these sort of more complex packages, if they use multiple export sets, they would want to first find all the dependencies for all export sets before creating targets for any of the export sets. This would then allow the package to properly honour the "we either fully succeed, or we don't create any targets and CMake can move on to trying to find another location that provides this package" (also see #25756 (closed)). This suggests that the find_dependency() calls should not be in the same file as the generated export file. They need to be separate, and most likely there needs to be a way to generate the final <package>-config.cmake file in such a way that all the find_dependency() calls get inlined so that failing to find any dependency will early-exit the <package>-config.cmake file. Perhaps this could be incorporated into the configure_package_config_file() command from the CMakePackageConfigHelpers module. We could add something like a DEPENDENCY_EXPORTS option to configure_package_config_file(), and the project would list the export sets after it. The command would then query internal information recorded somewhere by install(EXPORT... EXPORT_PACKAGE_DEPENDENCIES) for each of those export sets, find each one's generated dependencies file, and inline them into the generated <package>-config.cmake file. Note that this also means install(EXPORT) could be modified to always generate a dependencies file and not require a new EXPORT_PACKAGE_DEPENDENCIES keyword. We could allow such a keyword to instead customise the name of the dependencies file, but we can provide a sensible default based on the file name of the export itself.
I'm sure there are other possible strategies for resolving this, but it does seem to me like embedding the find_dependency() calls in the export file is the wrong place, except for the special case of there only being one export file and that export file being the <package>-config.cmake file directly. If we still want to support this special case, we likely need a keyword in the install(EXPORT) call to indicate whether the export file will indeed be the <package>-config.cmake file rather than being include()'d by it.