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] )
Helper IIHi daXtreme
Thank you very much for your reply and explanation. I tried out your measure, and it solved the job. In the meantime I managed to find a solution as well which gives the same result as yours once the Contract name slicer is applied.
I think it would be super interesting to look into what the differences are and how the influence performance.
This is the measure I used:
Contract Accountable =
VAR Contract_Accountable =
CALCULATE(
MAX(Dim_Contract_Table[Contract Accountable]),
FILTER(
'Fact_Control_Table',
'Fact_Control_Table'[Contract Name] = _Measures[Selected Contract Name])
)
Return
IF(
ISBLANK(Contract_Accountable), "No registrations", Contract_Accountable)
I used MAX in the first place since I need to use an aggregator when using CALCULATE on a column in a measure. I don't know other workarounds or best practices for acheiving the same result.
Regarding your question concerning the one-to-many relationship it shouldn't be an issue looking up the contract accountable in the Dim table, because it only contains one unique row per contract name.
daXtremeSolution Sage
The measure you've presented is bad for the simple reason that you've put the full extended fact table in FILTER and then use it as an argument in CALCULATE. This has many very unpleasant consequences. In fact, so many that it would take an entire chapter in a book to explain. Please, don't do this.