An Unexpected Exception Occurred error message on PBI Desktop only

[robmarsh]

I have a very strange error that occurs on Power BI Desktop only, when I publish the report to the service it runs fine. The error occurs when I add a particular measure to any visual. The error looks like below:

And when I click on See Details:

When testing the measure in question, it gets even weirder. Here is the code for the measure:

Conversion Count Numerator = 

VAR mindate = MIN('DateTable'[Date])
VAR maxdate = MAX('DateTable'[Date])

VAR tocount_oil = CALCULATE(
    COUNT('All Transactions'[TRANS_REF_NO]),
    REMOVEFILTERS('DateTable'[Month Date First], 'DateTable'[Date]),
    'All Transactions'[TRANS_TYPE] = "Quotation", 
    'All Transactions'[TRANS_DATE] >= mindate,
    'All Transactions'[TRANS_DATE] <= maxdate,
    'All Transactions'[TRANS_DATE_CLOSED] >= mindate,
    'All Transactions'[TRANS_DATE_CLOSED] <= maxdate,
    'All Transactions'[TRANS_STATUS] = "CLOSED WON",
    'All Transactions'[TRANS_DIVISION] = "Oil"
)

VAR tocount_dust = CALCULATE(
    COUNT('All Transactions'[TRANS_REF_NO]),
    REMOVEFILTERS('DateTable'[Month Date First], 'DateTable'[Date]),
    'All Transactions'[TRANS_TYPE] = "Quotation", 
    'All Transactions'[TRANS_DATE] >= mindate,
    'All Transactions'[TRANS_DATE] <= maxdate,
    'All Transactions'[TRANS_DATE_CLOSED] >= mindate,
    'All Transactions'[TRANS_DATE_CLOSED] <= maxdate,
    'All Transactions'[TRANS_STATUS] = "CLOSED WON",
    'All Transactions'[TRANS_DIVISION] = "Dust"
)

VAR tocount = tocount_oil + tocount_dust

RETURN tocount

When I return the variable tocount, I get the afforementioned error. If I return tocount_oil or tocount_dust, there is no error and the visual displays as expected. So the issue seems to be occurring when trying to add the two variables together. I noticed that tocount_oil returned a [Blank] value, so I tried using IF() and ISBLANK() to convert potential blank values to zeroes, which worked when returning tocount_oil or tocount_dust individually, but gave the same error when trying to return tocount.

Has anyone come across similar issues, and also the weird fact it works when published in the service?

[robmarsh]

Quick update: I managed to get the errors fixed by changing the column filters from being separate (with a comma) to being compounded (with &&) like below:

VAR tocount_oil = CALCULATE(
    COUNT('All Transactions'[TRANS_REF_NO]),
    REMOVEFILTERS('DateTable'[Month Date First], 'DateTable'[Date]),
    'All Transactions'[TRANS_TYPE] = "Quotation" &&
    'All Transactions'[TRANS_DATE] >= mindate &&
    'All Transactions'[TRANS_DATE] <= maxdate &&
    'All Transactions'[TRANS_DATE_CLOSED] >= mindate &&
    'All Transactions'[TRANS_DATE_CLOSED] <= maxdate &&
    'All Transactions'[TRANS_STATUS] = "CLOSED WON" &&
    'All Transactions'[TRANS_DIVISION] = "Oil"
)

 It would still be nice to understand why the original code caused the error, or whether it is a bug in Power BI. The fact that the report worked fine when published to the service suggests the latter, but history makes it odds on that my code was incorrect, so I am keen to understand why it failed if anyone has any insight?

[srbhdix]

just to check, did you tried having them as seperate measure and then create a new one to sum up the value? is this working or giving an error?

[robmarsh]

@srbhdix this just gets weirder still, although fits with the original issue. I separated out the two variables into two different measures. If I add either measure to a table on its own, the measure displays fine. If I try and add moth measures to the table as two separate columns, I get the error!

[robmarsh]

In addition to the suggestion from @srbhdix, I have also tried using the CONVERT() function...

VAR tocount = CONVERT(tocount_oil, INTEGER)  + CONVERT(tocount_dust, INTEGER)

RETURN tocount

Unfortunately, I get the same error!

[srbhdix]

You can try using Format function and convert value to number and then you can do the sum. 

Conversion Count Numerator = 

VAR mindate = MIN('DateTable'[Date])
VAR maxdate = MAX('DateTable'[Date])

VAR tocount_oil = Format(
CALCULATE(
    COUNT('All Transactions'[TRANS_REF_NO]),
    REMOVEFILTERS('DateTable'[Month Date First], 'DateTable'[Date]),
    'All Transactions'[TRANS_TYPE] = "Quotation", 
    'All Transactions'[TRANS_DATE] >= mindate,
    'All Transactions'[TRANS_DATE] <= maxdate,
    'All Transactions'[TRANS_DATE_CLOSED] >= mindate,
    'All Transactions'[TRANS_DATE_CLOSED] <= maxdate,
    'All Transactions'[TRANS_STATUS] = "CLOSED WON",
    'All Transactions'[TRANS_DIVISION] = "Oil"
),"0")

VAR tocount_dust = Format(
CALCULATE(
    COUNT('All Transactions'[TRANS_REF_NO]),
    REMOVEFILTERS('DateTable'[Month Date First], 'DateTable'[Date]),
    'All Transactions'[TRANS_TYPE] = "Quotation", 
    'All Transactions'[TRANS_DATE] >= mindate,
    'All Transactions'[TRANS_DATE] <= maxdate,
    'All Transactions'[TRANS_DATE_CLOSED] >= mindate,
    'All Transactions'[TRANS_DATE_CLOSED] <= maxdate,
    'All Transactions'[TRANS_STATUS] = "CLOSED WON",
    'All Transactions'[TRANS_DIVISION] = "Dust"
),"0")

VAR tocount = tocount_oil + tocount_dust

RETURN tocount

[robmarsh]

Thanks for the rapid reply 😀

I have tried as you suggested, but get the same error! I have never had an issue with the implicit conversion before, but thought that might have something to do with it which is why I went down the ISBLANK route to convert blank values to zeroes.