topic Re: Alternative for RELATED function in DAX Commands and Tips

Alternative for RELATED function

Anonymous — Wed, 01 Apr 2020 19:43:25 GMT

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"

)

Re: Alternative for RELATED function

MattAllington — Thu, 02 Apr 2020 08:35:57 GMT

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

Re: Alternative for RELATED function

Pragati11 — Thu, 02 Apr 2020 08:36:24 GMT

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

Re: Alternative for RELATED function

Anonymous — Thu, 23 Apr 2020 18:02:28 GMT

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.