Forum Discussion
Find Stage from Score Table
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
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,
1 Reply
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,