Conflicting Results from Dim Table Calculated Column

[amtbew]

I'm trying to add a column to a dim table that checks to see if a certain type of spend is present in my fact table. I'm getting incorrect info back and not sure what the reason.

I have a simple measure to sum spend in 2 different categories.

Spend - Category 1 = 
CALCULATE(
	SUM('Spend History'[Total Billed ($)]),
	'Spend History'[Work Order Type] IN { "Recurring Service", "Preventive Maint" }
)

I added this column to my dim table (Site Information).

Has Category 1 Spend = if(Calcs[Spend - Category 1]>0,"true","false")

Site Information and Spend History are linked by a many to one relationship "Site ID"

Here's the outcome as expected

However, if this particular Site ID reports as true, yet when you click on it you can see there is no actual spend in those categories.

Any ideas?

[nsexton12]

I know it is not usually recommended, but could you change your relationship to many-to-many and see if that fixes your issue? It looks like a relationship problem at first glance. If not, please let me know and I will test alternative solutions.

Please mark as a solution if this worked for you!

[amtbew]

I tried Many to Many and it didn't change the outcome. 

[nsexton12]

I recreated your data in my file and it all worked properly. Maybe it is the syntax of your calculated column? 

I am not sure why you have Calcs[Spend-Category 1] when it is just a measure. Can you try just typing in [Spend-Category 1]? My calculated column was:  IF([Measure 1] > 0 , "true","false").

[amtbew]

My measure is in a measure table called Calcs. 

I tried moving the measure out of that table and referencing it like you said and it still returns the same result. 

[nsexton12]

Can you scrub sensitive data and attach your PBIX file? I can't recreate your issue on my end and will need to see your full file unless someone else sees an error. 

[nsexton12 · Accepted solution]

I figured out your issue. Since you have that formula in a calculated column, it is not being filtered by the filters you have on the page. This is because the result is calculated in the data model instead of on the page. So, if you delete your calculated column, use that DAX code in a measure, and then drag that measure into your DIM visual, it will work. 

Please mark as a solution if this answered your question!