DAX measure - Return single value matching shared columns between two related tables + slicer value

[Magnus-CPH-DK]

Hi all,

My task is pretty straight forward, yet I cannot figure out the solution:

I want to create a measure that finds (CALCULATE?) the Contract Accountable in Dim_Contract_table given the sliced value Contract Name in Fact_Control_Table and by matching Control ID A which exists in both tables (And which is the unique PK in Dim_Contract_Table).

This is a mockup of the data model:

I have searched for different functions that I thought could be useful such as RELATED and LOOKUPVALUE but I wasn't able to get them to work. 

I thought of something like this:

Contract Accountable =
VAR Con_Acc =

CALCULATE(
   MAX(Dim_Contract_Table[Contract Accountable]),
      FILTER(
         Fact_Control_Table,
         Fact_Control_Table[Contract Name] = SELECTEDVALUE(Fact_Control_Table[Contract Name]))
            FILTER(
               Dim_Contract_Table,
               Dim_Contract_Table[Control ID A] = LOOKUPVALUE(
               Fact_Control_Table[Control ID A], 
               Dim_Contract_Table[Control ID A], Dim_Contract_Table[Control ID A])
               )
            )
RETURN
Con_Acc

Any ideas?

Best Regards,

Magnus

[Magnus-CPH-DK]

Hi @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. 

[daXtreme]

@Magnus-CPH-DK 

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.

[daXtreme · Accepted solution]

@Magnus-CPH-DK 

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]
)