Find Stage from Score Table

[Anonymous]

Hi All,

I have 2 tables. 1 is the SCORE Table and 1 is the MAIN table. Both are linked together in Diagram view. One to Many.

MAIN Table has 1 column called scored that varies between 0 to 600

SCORE Table has 3 columns From, To, Stage as per below

FROM - TO  - STAGE

0         - 99   - Stage 1

100     - 199 - Stage 2

200     - 299 - Stage 3

300     - 399 - Stage 4

400     - 499 - Stage 5

500     - 599 - Stage 6

600     -        - Stage 7

What I want to do is create a measure and a calculated column (both because to show how it works) that will calculate the values in the MAIN table basis the grid in the SCORE table and provide the relevant Stage from the SCORE table

How do I go about doing this?

Thanks

[v-jiascu-msft · Accepted solution]

Hi @Anonymous ,

I'm afraid the relationship can't be established based on your data here. Please download the demo from the attachment. 

The calculated column formula.

Column =
CALCULATE (
    MIN ( Score[STAGE] ),
    FILTER (
        Score,
        Score[FROM] <= Main[Score]
            && IF ( ISBLANK ( Score[TO] ), 9999999, Score[TO] ) >= Main[Score]
    )
)

The measure formula.

Measure =
CALCULATE (
    MIN ( Score[STAGE] ),
    FILTER (
        Score,
        Score[FROM] <= MIN ( Main[Score] )
            && IF ( ISBLANK ( Score[TO] ), 9999999, Score[TO] ) >= MIN ( Main[Score] )
    )
)

Best Regards,