cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wevernde
New Member

DAX error catch and continue, filter returns multiple values

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

1 ACCEPTED SOLUTION
OwenAuger
Super User I
Super User I

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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

2 REPLIES 2
wevernde
New Member

Thanks. That worked.

OwenAuger
Super User I
Super User I

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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors