Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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
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.
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
22 | |
10 | |
10 | |
9 | |
7 |