DAX calculate formula with filters...I'm Confused

[bdd9]

I have successfully written a formula that returns sales quantity from my fact table. the filters in the formula work even though my fact table (NS_Transactions) DOES NOT have a relationship with my dimension item table (NS_Items). However, when I create the relationship between the 2 tables, the formula breaks and returns nothing.  

Why would the filters and the formula work BEFORE there is even a relationship but not AFTER?

Here is the formula:

SalesQty_m = CALCULATE(

-sum(NS_Transactions[Qty]),

filter(NS_ITEMS,NS_ITEMS[TYPE_NAME]="Inventory Item"),

FILTER(NS_Accounts,NS_Accounts[TYPE_NAME]="Income")

)

Here is the accurate results BEFORE I create the relationship:

HEre is blank result I get after:

Here is a screenshot of the the relationship

[prateekraina]

Hi @bdd9,

Check relationship with NS_Accounts table. 

Prateek Raina

[bdd9]

Thank you for replying and offering help 🙂

A relationship does exist. 

I double checked the relationship between ns_transactions and ns_accounts. Here is a screenshot of it:

[bdd9 · Accepted solution]

Mystery solved...

The formula works perfectly...

The problem was I passed incorrect Type_Name in the filter for the item. Hence, the reason I was getting correct results without the item relationship was because that filter was not working anyway. In essence I didn;t need that filter to get the correct result AND I was using an incorrect parameter in the filter

Once I corrected that issue, I get the desired result

Thanks for your help and patience