Measure or Column with Bridge Table

[Anonymous]

This should be simple as it is a wash in SQL. However I am having no luck. TABLE A has measured DEPTHS per RAIL. TABLE B assigns COLOR between TOP and BOTTOM depths. Because this is a many-to many relationship, I created a bridge table (TABLE C) with one to many joins to both the A and B tables. The end result I am trying to achieve is that if the Table A RAIL = TABLE B RAIL and the TABLE A DEPTH falls between the TABLE B TOP and BOTTOM depths then post the TABLE B COLOR. I either get blanks or True/False. Please provide me the correct DAX formula to accomplish this.

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Color = 
CALCULATE ( MAX ( 'Table B'[COLOR] ),
    FILTER ( ALL ( 'Table B' ),
        [RAIL] = EARLIER ( 'Table A'[RAIL] )
            && [TOP] <= EARLIER ( 'Table A'[DEPTH] )
            && [BOTTOM] >= EARLIER ( 'Table A'[DEPTH] )
    )
)

Measure = 
CALCULATE ( MAX ( 'Table B'[COLOR] ),
    FILTER ( ALL ( 'Table B' ),
        [RAIL] = SELECTEDVALUE( 'Table A'[RAIL] )
            && [TOP] <= SELECTEDVALUE ( 'Table A'[DEPTH] )
            && [BOTTOM] >= SELECTEDVALUE( 'Table A'[DEPTH] )
    )
) 

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[FreemanZ]

hi @Anonymous 

try to create a calculated column in TableA like:

COLOR =

VAR _depth = [DELPTH]

VAR _rail = [RAIL]

VAR _table =

FILTER(

    TableB,

    TableB[RAIL] = _rail

         &&TableB[TOP] >= _depth

         &&TableB[BUTTOM] <= _depth

)

RETURN

MINX(_table, TableB[COLOR])

You don't need the bridge table in this way.