Unique value: use "valid from / valid to" for unique value

[RemiAnthonise]

I have a table called 'PositionWorkerAssignment' with the value PositionID. This field is the 1:N in some relationships.

Since today, I have the following problem. This field used to be unique bit it isn't anymore. The PositionID belongs to an employee (personnelnumber) but when this certain employee changes jobs / leaves company etc. the PositionID can belong to someone else. See the below image. 
The PositionID isn't unique anymore but the combination with Personnelnumber and Valid From / Valid To will always be unique.
I get my data from AX365 and that works with a ValidTimeState > a combination between Valid From / Valid To. As mentioned, this will always be unique. Is there a way to keep my relationships like this? Because in the future, I'll have to deal with this problem a lot. 

[RemiAnthonise · Accepted solution]

I found out it has something to do with slowly changing dimensions. See this thread for mor information.

url

[wildmight2017]

You can use filters to get rid of expired values. Either use Edit Queries or Power Query M to filter duplicates before they get to your data model. Another option is to use complex key (use ID + date field to join) - not as desirable. 

[RemiAnthonise]

Hi @wildmight2017 , I'm afraid filtering isn't an option since I get my data from Dynamics 365. This topic tells me I can't run a SQL query against the Dynamics 365 online data source.

I guess your other option 

---

@wildmight2017 wrote:

Another option is to use complex key (use ID + date field to join) - not as desirable. 

---

will be the only thing that will work in this case. I'm about to check this, I'll keep you posted. 

[RemiAnthonise]

I found this article from Radacad. I think this suits what we need. I'm about to try this one 🙂 . 

edit:

I guess this isn't the solution I'm looking for. I can merge 2 queries into 1 table to create a unique key but it's possible my data gets updated every day. After merging both queries the data won't be updated anymore (is my assumption). So: if my data get's updated  (I got new rows) these new fields won't appear in my merged table. Or is it possible to create a calculated field / write a formula which updates everytime there will be new data?

Below my datasets. 

In table A you see:

- A position is taken by 2 employess, see the different personnelnumber.
- You'll see that the timestamp is updated. So the combination of positionid, valid from, valid to and personnelnumber will always be unique. 

In table B you see:
- All mutations on a position. Position 1 is there multiple times with unique valid from / valid to. 
- The problem is: the time in the valid from / valid to fields doesn't match with table A. In table B I have all mutations on a position. In table A I have only updated rows when a position is assigned to a different employee.

I'm stuck with this problem and I'm looking for some advice. Thank you in advance. 

[wildmight2017]

One last thought: if you can use variable (such as CURRENT_DATE) in PowerQuery, you could potentially exclude items which aren't current. Check for Earliest/Latest filter in Edit Query please. For instance, Latest filter for Valid To will always give you current records.

Edit Queries -> Your table - > Transform Menu on top -> Date

Remy, is it an option for your to perform an intermediary basic ETL on this operational data to convert to DWH? Using SSIS or a free ETL tool to massage the data the way you need it  - this way also, if something gets changed, you will also keep history locally. 

One last thought: if you can use variable (such as CURRENT_DATE) in PowerQuery, you could potentially exclude items which aren't current. Check for Earliest/Latest filter in Edit Query please. For instance, Latest filter for Valid To will always give you current records.

Edit Queries -> Transform -> Date

[RemiAnthonise]

I've created a table with the following DAX:

Min max date position = SUMMARIZE(PositionDetails; PositionDetails[PositionId];"From"; MAX(PositionDetails[ValidFrom]); "To"; MAX(PositionDetails[ValidTo]); "Employee" ; MAX(PositionWorkerAssignments[PersonnelNumber])) 

---

This gives me the following table:

The DAX for "employee" isn't the right way because this gives me the MAX Employee number but in my current case there's only one Employee per position so for now it's alright. In the future I have to think about a better way. 

This isn't the solution to my problems in my previous post. I wrote this:

I guess this isn't the solution I'm looking for. I can merge 2 queries into 1 table to create a unique key but it's possible my data gets updated every day. After merging both queries the data won't be updated anymore (is my assumption). So: if my data get's updated  (I got new rows) these new fields won't appear in my merged table. Or is it possible to create a calculated field / write a formula which updates everytime there will be new data?

---

Is there a way to create a table that get's updated everytime there will be new data available? Like I said before: it's my assumption that a merged table from 2 queries won't be updated anymore after merging.