Same DAX Formula yielding different results - Column and Measure

[michael_knight]

Hi,

I'm trying create a column which uses the same formula as a measure I created. I'm using the same formula as the measure because I know it works. 

This is what the results are with the measure. The measure in question is "NEW MEASURE". These are the results I am after.

This is what the results are in the table. The colum in question is "NEW MEASURE COLUMN". I want the results to replicate those in the first image

NEW MEASURE = var acceptedmarket = SUM(Sheet1[Accepted Offer/Market Value])

var InsideOrOutside= 
IF(acceptedmarket <95, "Outside",
IF(acceptedmarket >105.51, "Outside",
IF(acceptedmarket <=105.50, "Inside"))) 


var AcceptedOffer = SUM(Sheet1[Accepted Offer])

return

IF(AcceptedOffer < 70000 && [DifferenceAcceptedSpread] <=5000, "Inside", InsideOrOutside)

The goal for this is to have a column of the results from the measure and then create a measure to count the number of  records that are"Inside" or "Outside" 

I'll add a PBIX of the data file

https://www.dropbox.com/s/dux2gr6bvf3vcd8/hELP.pbix?dl=0

Thank you,

Mike

[V-pazhen-msft · Accepted solution]

@michael_knight 

In you pbix, measure only considers the current property, column looks at all properties. 

To make them equivalent, you could add a filter in the column formula. 

NEW MEASURE COLUMN = 
var acceptedmarket = CALCULATE(SUM([Accepted Offer/Market Value]),ALLEXCEPT(Sheet1,Sheet1[Property]))

var InsideOrOutside= 
IF(acceptedmarket <95, "Outside",
IF(acceptedmarket >105.51, "Outside",
IF(acceptedmarket <=105.50, "Inside"))) 

var AcceptedOffer = CALCULATE(SUM(Sheet1[Accepted Offer]),ALLEXCEPT(Sheet1,Sheet1[Property]))

return
IF(AcceptedOffer < 70000 && [DifferenceAcceptedSpread] <=5000, "Inside", InsideOrOutside)

Best regards
Paul Zheng

[michael_knight]

Brilliant @V-pazhen-msft, exactly what I was after. Thank you very much 

And thank you too, @amitchandak. I appreciate the help guys

Thanks,

Mike

[amitchandak]

Column gets executed and values are stored in the table. It is pre-calculated. It always has a row context unless formula forces it ignores.

Measure, on the other hand, is the definition and what is the row context is very important.

Refer this: https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Also, check this file. How the 4 ways, measure way and column way of having date diff is different:https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

[amitchandak]

@michael_knight Does the above reply helps. if you need more help make me @

Appreciate your Kudos.