DAX bar is showing a red error, but not showing any reasoning and won't calculate

[paxtonmiller]

I am trying to create a formula that returns a date after original purchase. After I wrote the formula, the red bar in the DAX field is popping at red only at the very end of the formula (outside the last parenthesis), but doesnt give any indication to why.

Any suggestions would be much appreicated.

Customer Retention % = 
VAR CurrentMonthAfter = SELECTEDVALUE('Months After'[Value])
VAR CurrentFirstOrderMonth = SELECTEDVALUE('JOD REPORTS'[First Order Date (EOM)])
Return
DIVIDE(
    CALCULATE(
        DISTINCTCOUNT('JOD REPORTS'[Customer Number]),
        FILTER(
            'JOD REPORTS',
            EOMONTH('JOD REPORTS'[Order Date],0) = EOMONTH(CurrentFirstOrderMonth,CurrentMonthAfter)
        )
    ),
    DISTINCTCOUNT('JOD REPORTS'[Customer Number])
)

[Greg_Deckler]

@paxtonmiller To add to @ValtteriN I would rewrite it as the following (below). Then you can comment out code and change your RETURN statement to track down what VAR calculation is causing the issue. I suspect it is your numerator.

Customer Retention % = 
VAR CurrentMonthAfter = SELECTEDVALUE('Months After'[Value])
VAR CurrentFirstOrderMonth = SELECTEDVALUE('JOD REPORTS'[First Order Date (EOM)])
VAR __Nom = 
    CALCULATE(
        DISTINCTCOUNT('JOD REPORTS'[Customer Number]),
        FILTER(
            'JOD REPORTS',
            EOMONTH('JOD REPORTS'[Order Date],0) = EOMONTH(CurrentFirstOrderMonth,CurrentMonthAfter)
        )
    )
VAR __Denom = DISTINCTCOUNT('JOD REPORTS'[Customer Number])
VAR __Result = DIVIDE( __Nom, Denom )
Return
__Result

[paxtonmiller]

Greg, This was super helpful, and I think helped isolate. When I submit that formula, it returns saying failed to return "Denom" and that is isnt a table, data, etc.

I do note that there is no __ before the second denom, and I added that, which made the formula work, but didnt return any values.

Appreicate any further tips

[Greg_Deckler]

@paxtonmiller Yep, sorry, missed the two underscore characters. So what happens if in your RETURN statement you return __Nom or __Denom instead of __Result? You can also return your first two variables to make sure that those are correct as well. Is it possible that there are multiple values for those so SELECTEDVALUE is returning BLANK?

[paxtonmiller]

Thanks again.

When I change the calculate to the __nom, it returns all blanks.

When I change it to the __denom, it returns values. I assume that means the issue is coming from the nom. Does that mean there is a portion wrong with this part? 

        DISTINCTCOUNT('JOD REPORTS'[Customer Number]),

        FILTER(

            'JOD REPORTS',

            EOMONTH('JOD REPORTS'[Order Date],0) = EOMONTH(CurrentFirstOrderMonth,CurrentMonthAfter)

[Greg_Deckler · Accepted solution]

@paxtonmiller Yep, that is where your problem lies most likely. The way it is written, it looks like it is supposed to only return a value when the EOMONTH of the date is equal to the EOMONTH of the selected date and months after. It might be CALCULATE black-boxery going on here. You could try rewriting the measure like this for even more troubleshooting awareness as well as ridding yourself of CALCULATE which tends to trip people up a lot.

Customer Retention % = 
VAR CurrentMonthAfter = SELECTEDVALUE('Months After'[Value])
VAR CurrentFirstOrderMonth = SELECTEDVALUE('JOD REPORTS'[First Order Date (EOM)])
VAR __Date = EOMONTH(CurrentFirstOrderMonth,CurrentMonthAfter)
VAR __Table = FILTER( 'JOD REPORTS', EOMONTH('JOD REPORTS'[Order Date],0) = __Date )
VAR __Nom = COUNTROWS( DISTINCT( SELECTCOLUMNS( __Table, "__CustomerNumber", [Customer Number] ) ) ) )
VAR __Denom = DISTINCTCOUNT('JOD REPORTS'[Customer Number])
VAR __Result = DIVIDE( __Nom, __Denom )
Return
__Result

[ValtteriN]

Hi,

On a first glance the formula seems correct, because of this the problem might be due to invisible character or maybe your selectedvalue is returning blank. To troubleshoot I would recommend testing the formula with fixed values in variables e.g. 1 for currentmonth after and double checking the formula in a text editor like notebook++. 

One other reason could be some wierd cached data. 

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/