Mimic RELATED through bridge table in DAX

Question

I'm building SSAS Tabular Model for MySQL-based SugarCRM application. One of unpleasant SugarCRM features is creating a bridge table (i.e. in fact many-to-many relationship) in cases where real relationship is just one-to-many. This redundant bridge table causes a problem when I need to get a value from the table on one side into calculated column in table on many side.

I tried something like as calculated column in table on many side:

=
CALCULATE (
    VALUES ( 'OneSideTable'[field_i_need] ),
    FILTER (
        'OneSideTable',
        CALCULATE (
            COUNTROWS (
                FILTER (
                    'BridgeTable',
                    'BridgeTable'[ManyTableID] = ManyTable[ID]
                )
            )
        )
            = 1
    )
)

But it throws an error:

The value for column 'ID' in table 'ManyTable' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

Are there any ways to mimic RELATED function in this particular case?

erop erop · Accepted Answer · 2015-09-28T11:26:11

Looks like I found solution and it seems a little simplier that I thought before:

=
CALCULATE (
    VALUES ( 'BridgeTable'[OneTableID] ),
    FILTER (
        RELATEDTABLE ( 'BridgeTable' ),
        'BridgeTable'[deleted] = 0
    )
)

Pay attention to using 'BridgeTable'[deleted] = 0 filtering rule to avoid getting inactive related OneTableID. This is particular SugarCRM-related feature.

Mimic RELATED through bridge table in DAX

1 Answers