Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Measure or Column with Bridge Table

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's avatar
    v-zhangti
    3 years ago

    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.

     

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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.
  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    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.