Solved: Restrict the range of the Number data type - Page 2

Quentinlebg · 07-06-2021 08:59 AM

So I have been trying to change the range of the number data type. I changed it manually at first, but as you can see with the table below, the hardwares don’t have the same limit.

Table Name: Hardware Discount
Hardware Minimum Maximum
Hardware#1 10,000 100,000
Hardware#2 10,000 100,000
Hardware#3 2,500 10,000

I tried using this:
IF([_THISROW].[Hardware] = “Hardware#1”,
ANY(SELECT(Hardware Discount[Minimum], [Hardware] = [_THISROW].[Hardware])),
ANY(SELECT(Hardware Discount[Maximum], [Hardware] = [_THISROW].[Hardware])))

But I keep getting “The expression is valid but its result type ‘Number’ is not one of the expected types: Yes/No, List”
I feel like even if I was not getting that error message, it would still not work.

Steve

Better:

ISNOTBLANK(
  FILTER(
    "Hardware Discount Volume",
    AND(
      ([Hardware] = [_THISROW].[Hardware]),
      ([_THIS] >= [Lower]),
      ([_THIS] <= [Higher])
    )
  )
)

Your expression uses LOOKUP() twice to find the same row, which is two scans of the table. Mine scans the table only once.

View solution in original post