Glad to hear that you are reworking the data model. The fact that I had to create a “Cannot Be Fulfilled” bucket in the above DAX measure was a flashing red siren indicating that ship was going down (over time every row of the table would fall into that bucket).
My recommendation would be to forget about any more DAX at this point and focus on developing:
-
plain English specification of the questions you ultimately want to be able to answer (i.e., analysis plan). Here’s an example of the type of analysis planning that I do in advance of data modeling. There are often many ways to construct a star schema model for a given project, and a sound analysis plan provides a great blueprint for that construction.
-
build the star schema data model that will support answering those questions
Once you’ve done these things, particularly if you’ve pushed everything upstream to Power Query that should be done in the data prep stage, your DAX will likely be much simpler and more intuitive.
Since I’ve started focusing on doing things that way, I rarely have to venture into complex DAX in my own reports. In fact, one of the reasons I enjoy the forum so much is that it helps me keep my DAX skills sharp, since in my own reports most of the DAX is pretty plain vanilla, because I’m letting the analysis planning, Power Query and the data modeling carry the bulk of the load. DAX is used primarily for supporting the UX, conditional formatting, and anythiing that needs to be dynamic within the course of a reporting session.
Hope this is helpful.
- Brian