Calculate, Sum, Filters - Dax Query

[JKHATRAK]

Hi, How do i correct below formula in Power Bi. Criteria is: I need Sum of (USD) where we have not contacted customer for more than 15 days with specific excuse code description as mentioned in below formula with specific Ageing Bucket.

I am getting below error. Please let me know what changes should be done to correct the formula.

Error: True/False expression does not specific a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

Last Engagement Made Greater than 15 days = CALCULATE(SUM('Raw Data'[USD]),'Raw Data'[Days since last engagement] = "Greater than 15days",'Raw Data'[Excuse Code Description] = "Business Interruption","Contact Made","Credit Balance Known to customer","0.Default","9.Govmt/Institution Slow Pay/Funding","7.Payment Awaiting Approval","11.Pending Support from Business","4.Customer Researching","5.Credit Balance,In Research","6.Unresponsive Customer","1.Initial Contact pre due date",'Raw Data'[Pocket] = "1-30","31-60","61-90","91-120","Over 120")

[v-yiruan-msft · Accepted solution]

Hi @JKHATRAK ,

Please update the formula of the measure [Last Engagement Made Greater than 15 days] as below and check if it works or not... 

Last Engagement Made Greater than 15 days =
CALCULATE (
    SUM ( 'Raw Data'[USD] ),
    FILTER (
        'Raw Data',
        'Raw Data'[Days since last engagement] = "Greater than 15days"
            && 'Raw Data'[Excuse Code Description]
            IN {
            "Business Interruption",
            "Contact Made",
            "Credit Balance Known to customer",
            "0.Default",
            "9.Govmt/Institution Slow Pay/Funding",
            "7.Payment Awaiting Approval",
            "11.Pending Support from Business",
            "4.Customer Researching",
            "5.Credit Balance,In Research",
            "6.Unresponsive Customer",
            "1.Initial Contact pre due date"
        }
            && 'Raw Data'[Pocket] IN { "1-30", "31-60", "61-90", "91-120", "Over 120" }
    )
)

And you can use DAX Formatter to format your formula and check there is no syntax error...

If the above one can't help you get the desired result, please provide some sample data in your table 'Raw data' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[JKHATRAK]

Thank you Very Much. This works 🙂

[Greg_Deckler]

@JKHATRAK Hard to tell exactly but that is definitely incorrect syntax. Try one of these:

Last Engagement Made Greater than 15 days = 
	CALCULATE(
		SUM('Raw Data'[USD]),
		'Raw Data'[Days since last engagement] = "Greater than 15days",
		'Raw Data'[Excuse Code Description] IN { "Business Interruption","Contact Made","Credit Balance Known to customer","0.Default","9.Govmt/Institution Slow Pay/Funding","7.Payment Awaiting Approval","11.Pending Support from Business","4.Customer Researching","5.Credit Balance,In Research","6.Unresponsive Customer","1.Initial Contact pre due date" },
		'Raw Data'[Pocket] = "1-30","31-60","61-90","91-120","Over 120"
	)


Last Engagement Made Greater than 15 days = 
  VAR __Table =
	FILTER('Raw Data',
		'Raw Data'[Days since last engagement] = "Greater than 15days",
		'Raw Data'[Excuse Code Description] IN { "Business Interruption","Contact Made","Credit Balance Known to customer","0.Default","9.Govmt/Institution Slow Pay/Funding","7.Payment Awaiting Approval","11.Pending Support from Business","4.Customer Researching","5.Credit Balance,In Research","6.Unresponsive Customer","1.Initial Contact pre due date" },
		'Raw Data'[Pocket] = "1-30","31-60","61-90","91-120","Over 120"
	)
RETURN
  SUMX(__Table, [USD])