Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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 3
Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

 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
Community Champion
Community Champion

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.