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

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

paxtonmiller — Sun, 01 Sep 2024 15:39:19 GMT

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

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

ValtteriN — Sun, 01 Sep 2024 16:04:20 GMT

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/

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

Greg_Deckler — Sun, 01 Sep 2024 16:08:28 GMT

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

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

paxtonmiller — Sun, 01 Sep 2024 16:52:12 GMT

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

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

Greg_Deckler — Sun, 01 Sep 2024 17:12:48 GMT

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?

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

paxtonmiller — Sun, 01 Sep 2024 17:24:48 GMT

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)

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

Greg_Deckler — Sun, 01 Sep 2024 17:43:05 GMT

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