If value is between two numbers from another table

[EZiamslow]

Look up table:

Product	StartStep	EndStep
Apple	1	7
Apple	19	36
Apple	41	46
Apple	54	61
Orange	1	11
Orange	35	42
Orange	55	62

Fact table:

Product	Step	YN	Qty
Apple	6	Yes	5
Apple	8	No	5
Apple	60	Yes	4
Orange	12	No	6
Orange	45	No	6
Orange	55	Yes	4

Result:

Apple = 9 qty (5+4)

Orange = 4 qty (4)

I'm trying to create a YN calculated column in the fact table. Yes/No if Step from fact table is between StartStep and EndStep from look up table. Then, filter YN column to calculate the total qty of the products.

What would be the best way to approach this? 

[CNENFRNL]

[VahidDM · Accepted solution]

HI @EZiamslow 

Trty this code to add a new column by using DAX:

YN Column = 
VAR _Step = 'Fact'[Step]
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Look up' ),
            FILTER (
                'Look up',
                'Look up'[Product] = EARLIER ( 'Fact'[Product] )
                    && 'Look up'[EndStep] >= _Step
                    && 'Look up'[StartStep] <= _Step
            )
        ) > 0,
        "Yes",
        "No"
    )

Output:

Sample file attached for your reference

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube