{"id":3970,"date":"2022-01-30T00:32:18","date_gmt":"2022-01-30T08:32:18","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3970"},"modified":"2022-01-30T00:32:21","modified_gmt":"2022-01-30T08:32:21","slug":"how-to-fix-a-function-calculate-has-been-used-in-a-true-false-expression-that-is-used-as-a-table-filter-expression-this-is-not-allowed","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3970","title":{"rendered":"How to Fix “A function ‘CALCULATE’ has been used in a True\/False expression that is used as a table filter expression. This is not allowed.”"},"content":{"rendered":"\n

Sample problem:<\/p>\n\n\n\n

I’m looking for a more elegant way sum based on a prior business day calculation.  The following *does work* but will get a little more cumbersome when doing MTD or YTD calculations:<\/p>\n\n\n\n

Prior Day Invoiced = CALCULATE(sum(‘Sales Ledger Transactions'[Net Sales]),’Sales Ledger Transactions'[Fact Attributes.Source Sale Type Code] = “INVOICED”,
‘Sales Ledger Transactions'[Date.Date] = (if(weekday(today(),1)=1,today() – 2,
if(WEEKDAY(today(),1)=2,today()-3, today()-1))))<\/p>\n\n\n\n

What I’d like to do (see below) blows up with the error “A function ‘CALCULATE’ has been used in a True\/False expression that is used as a table filter expression. This is not allowed.<\/strong>“.\u00a0 This seems pretty simple but I can’t seem to make it work:<\/p>\n\n\n\n

Prior Day Invoiced = CALCULATE(sum(‘Sales Ledger Transactions'[Net Sales]),’Sales Ledger Transactions'[Fact Attributes.Source Sale Type Code] = “INVOICED”, ‘Sales Ledger Transactions'[Date.Date] = [Priorbusinessday])<\/p>\n\n\n\n

Priorbusinessday = if(weekday(today(),1)=1,today() – 2, if(WEEKDAY(today(),1)=2,today()-3, today()-1))<\/p>\n\n\n\n

Is there a more elegant way to use the Priorbusinessday measure or will I have to use the first method?<\/p>\n\n\n\n

<\/p>\n\n\n\n

Solutions:<\/p>\n\n\n\n

Setting the measure equal to a variable first. Because ‘Calculate” is not accepting a true\/false expression as a filter.<\/p>\n\n\n\n

Prior Day Invoiced =\nVAR PreviousBusinessDay = [Priorbusinessday]\nRETURN\n    CALCULATE (\n        SUM ( 'Sales Ledger Transactions'[Net Sales] ),\n        'Sales Ledger Transactions'[Fact Attributes.Source Sale Type Code] = \"INVOICED\",\n        'Sales Ledger Transactions'[Date.Date] = PreviousBusinessDay\n    )<\/em><\/pre>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Sample problem: I’m looking for a more elegant way sum based on a prior business day calculation.  The following *does work* but will get a little more cumbersome when doing MTD or YTD calculations: Prior Day Invoiced = CALCULATE(sum(‘Sales Ledger Transactions'[Net Sales]),’Sales Ledger Transactions'[Fact Attributes.Source Sale Type Code] = “INVOICED”,‘Sales Ledger Transactions'[Date.Date] = (if(weekday(today(),1)=1,today() – Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[621],"tags":[1088,1087],"class_list":["post-3970","post","type-post","status-publish","format-standard","hentry","category-power-bi","tag-calculate-has-been-used-in-a-true-false-expression-2","tag-calculate-not-accepting-a-true-false"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t