Forum Discussion
Help With DAX calculated column finding the earliest Date?
Thank You Jahida, That looks like it worked. Here is a copy of the me imputting this into my dashboard.
No problem. You killed the pronounciation the first time, then corrected yourself wrong haha.
I'm not an expert on this (many other people on this forum are experts by comparison), but here's my take:
DAX I find much easier to write. It's flexible, in my experience executes quickly on refresh, and if you use measures it can be dynamic.
M is a bit harder to write, for me at least. However, any processing you do in M, although it may not necessarily be faster, is better for the size of your file because 1) you can filter out unnecessary information before it loads and 2) columns created in M or brought in straight from the source are stored more efficiently than ones created in DAX (I just learned that yesterday). Also, M can be faster by pushing calculations back into the database at times. This, in my experience, is very inconsistent, but at times a great speedup.
Basically for a user just starting up, I would recommend doing filtering in M and nearly everything else in DAX, unless your dataset is absolutely massive and size is a massive concern.
Many people in the forum will protest that anything other than a Date table and calculated measures in DAX is bad style, at some point it does come down to personal preference and an ease of use vs. efficiency trade-off.
I'm sure there is a solution to this problem in M. It would take me a while to write though since I'm much more comfortable in DAX.
Impactful IndividualTry making a calculated column with the formula (make sure Estimate Date is the correct type):
Column = CALCULATE(MIN(Table1[Estimate Date]), ALL(Table1), Table1[opportunity #] = EARLIER(Table1[opportunity #]))
Hi Jahida, This deffinetly looks like it is taking me in the right direction. I feel good that it is possable :) I think I'm missing something very small here. Can you see where I'm going wrong?
- jahida
The formula I gave was in DAX, this looks like you're in Query Editor (language = M). In order for my formula to work, you'll need to get out of Query Editor, go to the Data view in the main PowerBI window, navigate to the correct table, and go to
Modelling -> New Column. Then you can enter my formula and make the adjustments as you did.
Hope that helps!
Thank You Jahida, That looks like it worked. Here is a copy of the me imputting this into my dashboard.