Alternative for RELATED function

[Anonymous]

In the simplest sense I have two tables as follows.  Live Data is as it sounds, records with current data.  History takes a snapshot of open records on the first of each month.

Live Data

ID	Location	Created	Current Status
1	N	12/15/2019	CLOSE
2	S	1/2/2020	INPROG
3	N	1/29/2020	INPROG

History

ID	Location	Date	Status
1	N	1/1/2020	CREATED
1	N	2/1/2020	INPROG
2	S	2/1/2020	CREATED
3	N	2/1/2020	CREATED
3	N	3/1/2020	INPROG

The tables are joined on ID & Location.  ID is the active relationship.  Location relationship is used to find records for new locations (i.e. not in the last snapshot).  I should also mention that there is also a calendar table that is joined to History table.

I am trying to add a Calculated Column in Live table that is equivalent to RELATED('History'[Status]) so that when I filter on a specific 'History'[Date] (naturally, throuh my Calendar date) that I get the Status that is applicable. 

I understand why that doesn't work but have not found a suitable substitute.  I was able to build this as a RELATED workaround for my Location issue but have not been able to adapt it to the Status. 

Historical Location? =

IF (

     CALCULATE ( DISTINCTCOUNT('History'[Location],

          FILTER('History','History'[Date] = LASTDATE('Calendar'[Date]) && 'Live'[Location] = 'History'[Location])

      ) > 0,

     "Y",

     "N"

)

[Pragati11]

Hi @Anonymous ,

Have you tried looking into LOOKUPVALUE dax function?

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Anonymous]

 Thanks for that but LOOKUPVALUE hasn't been any help.  I there is a cautionary note in The Definitive Guide to DAX that explains the reason why it's not my solution.  The note basically says that LOOKUPVALUE ignores existing filter context.  

Since I want/need the calendar dates to be in play here I can't use LOOKUPVALUE.

[MattAllington]

I would approach it differently. I would append live and history into a single fact table. Add a new column called status (or what ever) that stores either Live or History.   I would then create dimension tables for iD, loc. and date. I assume history date is the extract date, so you would end up with potentially 2 date columns. Add the current date for the live data. 

This will create a simple star schema, and the DAX should be easier. 

current records = calculate(countrows(data),data[status]="Live")

history records = calculate(countrows(data),data[status]="History")

etc