Check whether Value within certain Range saved in other Table

[Anonymous]

Hey there,

How can I check whether a value falls within a certain range, as soon as a certain ‘text’ field will be looked up in another table.

I tried to find the solution online, but unfortunately I couldn't find it (fortunately I did learn new things;))

Let me show what I mean:

Let’s take Supermarkets that sell Apples and Bananas as an example:

<Supermarket Products>

Supermarket, AppleSize, BananaSize, CherrySize

Supermarket1, Big, 200, Small

Supermarket2, Big, 400, Big

Supermarket3, Small, 100, Big

Supermarket4, Little, 90, Small

<Fruit Quality Parameters Table>

Category,Type,Size,IntervalFrom, IntervalTo

Fruit, Apple, Small, null, null

Fruit, Apple, Normal, null, null

Fruit, Apple, Big, null, null

Fruit, Banana, ALL, 100, 300

Fruit, Cherry, Small, null, null

Fruit, Cherry, Big, null, null

Apple should only have the labels ‘Small’, ‘Normal’ or ‘Big’.

Banana should only have a digit as a value, in this case it will resemble the length of min 100 until max 300.

Cherry should only have the label ‘Small’ or ‘Big’.

The following works for the Apple and the Cherry, but what to do with the Banana?

AppleCheck =

VAR appleSizes =

    CALCULATETABLE ( VALUES ( FruitTable[Size] ), 'FruitTable'[Type] = "Apple" )

RETURN

    IF ( [AppleSize] IN appleSizes, "Yes", "No" )

CherryCheck =

VAR CherrySizes =

    CALCULATETABLE ( VALUES ( FruitTable[Size] ), 'FruitTable'[Type] = "Cherry" )

RETURN

    IF ( [CherrySize] IN CherrySizes, "Yes", "No" )

And gives as a result that:

• Apples: 75% correct
• Cherry’s: 100% correct

We know that for Banana's 400 and 90 are outliers within the 100-300 range, and that the result will be 50%, but how to write this in DAX in a way that it takes the <Fruit Quality Parameters Table> into account. That as soon as the label ‘ALL’ shows up, it will check the interval columns.

Another issue related to this might be the following: the values of AppleSize, BananaSize and CherrySize are all registered in the same column… This might be an issue to check whether ‘90’ is between a certain range, since that row needs to be converted to a number.

Any ideas on this?

Kind regards,

Igor

[vanessafvg]

@Anonymous   could solution it for you and maybe its a long way around but i would remodel the data, if you need more help let me know

in query editor i would create a custom column that picks up the type from your supermarket table , and unpivot  the columns to the rows, ie. have a row for each product and the size, i would have a size text column and a size number column.  

so columns like this

supermarket, type, size text, size number

supermarket1, cherry, big, null

then create a lookup table in a separate table  of the distinct value of fruit ie click on the fruit column and say add as new query and remove duplicates

then join the 3 tables together via the lookup table, the way you can use the relationship to check the interval via related() function  

[Anonymous]

Ok, it took my quite a while to try things, since I had different priorities. Though, the focus lies on this now again.

And I understand what you are saying, though for me I don't really know how to get there. Is there a possibility you can get more concrete?

I'm willing to change the data model for sure.

Kind regards,

Igor