topic Calculate, Sum, Filters - Dax Query in DAX Commands and Tips

Calculate, Sum, Filters - Dax Query

JKHATRAK — Wed, 12 Oct 2022 07:34:35 GMT

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.

Re: Calculate, Sum, Filters - Dax Query

Greg_Deckler — Wed, 12 Oct 2022 07:58:57 GMT

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])

Re: Calculate, Sum, Filters - Dax Query

Anonymous — Thu, 13 Oct 2022 09:06:10 GMT

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

Re: Calculate, Sum, Filters - Dax Query

JKHATRAK — Thu, 13 Oct 2022 09:48:35 GMT

Thank you Very Much. This works 🙂