I’ll explain a technique I used recently to find an error in a DAX measure and a big hint that I missed!

File: Pbix

Set-up

Basic table viz with 2 measures.

Viz Error

I added measure ‘% of Prev (error trap)‘ that caused this error:

Test Each Variable

I commented out variable Final (adding “//” before it) and added ThisStage. I couldn’t see it’s value in the visual as the error continued to be displayed.

% of Prev (error trap) v2 = 
//Get stage number (current viz row)
VAR ThisStage = MIN('Funnel Chart'[Sales Stage Num])
VAR PrevStage = ThisStage-1

//Count rows for current stage & prev stage
VAR ThisStageCount = COUNTROWS('Funnel Chart')
VAR LastStageCount =
    IF(ThisStage = 1, BLANK(),
    COUNTROWS(FILTER(ALL('Funnel Chart'),'Funnel Chart'[Sales Stage]=PrevStage)))

//Final division
VAR Final =
DIVIDE(ThisStageCount,LastStageCount)

RETURN
//Final
ThisStage

DAX Syntax Error

Do I have a syntax error? I added block comments to temporarily turn off parts of my DAX. All the DAX between /* and */ is now a comment:

% of Prev (error trap) v3 = 
//Get stage number (current viz row)
VAR ThisStage = MIN('Funnel Chart'[Sales Stage Num])
VAR PrevStage = ThisStage-1

/*
//Count rows for current stage & prev stage
VAR ThisStageCount = COUNTROWS('Funnel Chart')
VAR LastStageCount =
    IF(ThisStage = 1, BLANK(),
    COUNTROWS(FILTER(ALL('Funnel Chart'),'Funnel Chart'[Sales Stage]=PrevStage)))

//Final division
VAR Final =
DIVIDE(ThisStageCount,LastStageCount)
*/

RETURN
//Final
ThisStage

Visual works and displays result of variable ThisStage! I then moved the comment block around and discovered that variable LastStageCount was causing the error!

Error Message: ‘See Details’

I should’ve clicked link for ‘See Details’ earlier. It gives a big hint:

DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values

In VAR LastStageCount I used the wrong field! Instead of ‘Funnel Chart'[Sales Stage] (text field) I should’ve used ‘Funnel Chart'[Sales Stage Num] (numeric field)!

‘Funnel Chart'[Sales Stage Num] is compared to VAR PrevStage. Both are numbers!

'Funnel Chart'[Sales Stage Num]=PrevStage

Fixed DAX Measure

This is the working measure and visual:

% of Prev = 
//Get stage number (current viz row)
VAR ThisStage = MIN('Funnel Chart'[Sales Stage Num])
VAR PrevStage = ThisStage-1

//Count rows for current stage & prev stage
VAR ThisStageCount = COUNTROWS('Funnel Chart')
VAR LastStageCount =
    IF(ThisStage = 1, BLANK(),
    COUNTROWS(FILTER(ALL('Funnel Chart'),'Funnel Chart'[Sales Stage Num]=PrevStage)))

//Final division
VAR Final =
DIVIDE(ThisStageCount,LastStageCount)

RETURN
Final

Summary

I learned a big lesson that error messages can be very helpful. The block comment technique is useful but I could have solved this quicker. This happened while creating the Funnel Chart post.

I created the error trying to compare a text value with a numerical value. Now it seems so silly but this is part of the learning process.