DAX error catch and continue, filter returns multiple values

[Anonymous]

Hi,

I have an error in my DAX that I would like to catch and continue. Perhaps you can help.

I have two tables. One is a list of numbers and the other is a number range with a description. Like this:

NumberTable

NumAsInt

3

25

75

DescriptionTable

Desc, StartNum, EndNum

“small”, 0, 33

“medium”,34,75

“large”,75,1000

My DAX is:

NumberDescription =CALCULATE(VALUES(DescriptionTable[Desc]),

                                                                              FILTER(DescriptionTable,

                                                                                            NumberTable[NumAsInt] >= DescriptionTable[StartNum] && NumberTableNumAsInt] <= DescriptionTable[EndNum]))

The problem is when NumAsInt is 75 two entries from the DescriptionTable are returned and I get an error in the formula. What I would like to do is ingnore that error and return blank for the description. The real problem of course is that I have overlapping ranges in my DescriptionTable but its just not practical to fix that. I played a bit with IF HASONEVALUE but couldn't get that to work. 

Thanks,

-Wes

[Anonymous]

Thanks. That worked.

[OwenAuger · Accepted solution]

Hi Wes,

From your post it appears that you're creating a calculated column in NumberTable, similar to a Static Segmentation pattern.

To fix your immediate issue, you could replace VALUES with SELECTEDVALUE, which will automatically return blank in the case of multiple matches.

You may want to take a look at Static Segmentation - DAX Patterns for more general discussion of this sort of pattern.

Regards,

Owen