With Sam indicating it’s always best to create a measure over a calculated column, I’m trying to figure out how to write a formula that will return a value of 1, 2, 3, or 4 based on the following logic
IF “Event Date” > today () +14 = 1
IF “Event Date” is between today () -7 & today () +14 = 2
IF “Event Date” is < today () -7 AND “Event Achieved” = “true” = 3
IF “Event Date” is (Blank) = 4
else 5
My thought is that I can use these values to create conditional formate based on these values
I have found in the past that measures may be the best option in most cases however too many statements and conditions in a DAX measure can lead to an inefficient model in terms of speed and experience of end-users.
The table sizes and dimensions of your model play an important part in determining whether a calculated column or measure is the best option in my opinion.
Sometimes a calculated column at load time provides a lot faster result and better UI experience.
You can use both Measure and Calculated column in this case using Switch statement instead of IF.
However, I personally prefer to use Calculated Columns where calculation needs to be performed for each Row
and output values don’t need to be aggregated.
Performance wise don’t think there will be much difference.
I for my particular situation I did go ahead and create a calculated column since @ankit was right that there was no need to aggregate the values. To get the solution to work I had to google examples of M code. Frustrating there is not a Today() function and it’s very case sensitive
@GarryA thanks so much for taking the time to reply. I’m very new to DAX and you gave me some things to think about.
@sam.mckay thanks for the SWITCH video. I have so much to learn and have seen that that function gets used a lot.
