Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 60...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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] )
    )
)

    Find-Stage-from-Score-Table

     

     

    Best Regards,

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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] )
    )
)

Find-Stage-from-Score-Table

 

 

Best Regards,