Forum Discussion
Helper IIDAX measure - Return single value matching shared columns between two related tables + slicer value
Since there's a one-to-many between the tables, how do you know which Contract Accountable you want to retrieve for a Contract Name? I can see you want to use MAX, but is it really what you want to do? There might be many of these for a single Contract Name. Your formula is too convoluted and, in fact, will be very, very slow on even moderately sized data sets. The principle of DAX is never to put an entire table as a filter. This is dangerous and leads to poor performance. A good formula uses only the columns it absolutely needs to. Something like:
MAX Contract Accountable = MAXX( summarize( Fact_Control_Table, Dim_Contract_Table[Contract Accountable] ), Dim_Contract_Table[Contract Accountable] )
Solution Sage
Since there's a one-to-many between the tables, how do you know which Contract Accountable you want to retrieve for a Contract Name? I can see you want to use MAX, but is it really what you want to do? There might be many of these for a single Contract Name. Your formula is too convoluted and, in fact, will be very, very slow on even moderately sized data sets. The principle of DAX is never to put an entire table as a filter. This is dangerous and leads to poor performance. A good formula uses only the columns it absolutely needs to. Something like:
MAX Contract Accountable =
MAXX(
summarize(
Fact_Control_Table,
Dim_Contract_Table[Contract Accountable]
),
Dim_Contract_Table[Contract Accountable]
)