Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Alternative for RELATED function

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

IDLocationCreatedCurrent Status
1N12/15/2019CLOSE
2S1/2/2020INPROG
3N1/29/2020INPROG

 

History

IDLocationDateStatus
1N1/1/2020CREATED
1N2/1/2020INPROG
2S2/1/2020CREATED
3N2/1/2020CREATED
3N3/1/2020INPROG

 

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

3 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    6 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       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.