Relationship and DAX formulas returns nothing

[GJA]

Hi,

I have a relationship which affects the results of my Dax Formulas. 

In the table "Equipment Header" I have informations about Equipments related to some days (not every day). I created a table "PNT_Control Equipment" in which I have one row per Equipment and day for each Equipment and each day. Both are linked with a bidirectionnal relationship on a key composed by date and Equipment ID.

When I create a column with DAX in "PNT_Control Equipment" in order to get the name of the of the Equipment (in the other table) for exemple, it returns nothing for the dates which doesn't exists in "Equipment Header".

I would like a result for each row. I see that if I delete the relationship it works but I need this relationship.

I have some troubles because on the other hand I have exactly the same relationship between other tables and it works.

I tried this simple formula for example : 

LASTNONBLANK('Equipment Header'[cle_pnt_control_equipment],1) Thanks in advance for your help

[DataInsights · Accepted solution]

Hi GJA,

My solution consists of three calculated columns in table PNT Control Equipment. The field Composite Key is composed of Date and Equipment ID. The concept of the solution is if there is no match in Equipment Header, get the most recent Date in Equipment Header for the current Equipment ID, create a new Composite Key with this Date, and then lookup the name of the equipment based on this new Composite Key.

DAX below so you can copy/paste:

Equipment Header Most Recent Date =
--see if there is a match on Date and Equipment ID
VAR EqHdrCompKey =
LOOKUPVALUE (
'Equipment Header'[Date],
'Equipment Header'[Composite Key], 'PNT Control Equipment'[Composite Key]
)
--if no match, get the most recent date for which there is a match on Equipment ID
VAR EqHdrTable =
FILTER (
'Equipment Header',
'PNT Control Equipment'[Equipment ID] = 'Equipment Header'[Equipment ID]
)
VAR MinDate =
MINX ( EqHdrTable, 'Equipment Header'[Date] )
VAR Result =
IF ( ISBLANK ( EqHdrCompKey ), MinDate, EqHdrCompKey )
RETURN
Result

Composite Key Most Recent Date = 'PNT Control Equipment'[Equipment Header Most Recent Date] & 'PNT Control Equipment'[Equipment ID]

Equipment Name =
LOOKUPVALUE (
'Equipment Header'[Equipment Name],
'Equipment Header'[Composite Key], 'PNT Control Equipment'[Composite Key Most Recent Date]
)

Hope this resolves the issue.

[GJA]

Thank you it solved my problem