cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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

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. 

 

errortable.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper V
Helper V

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

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

url

View solution in original post

6 REPLIES 6
Highlighted
Advocate II
Advocate II

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

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. 

Highlighted
Helper V
Helper V

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

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. 

Highlighted
Helper V
Helper V

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

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. 

 

data position.jpg

Highlighted
Advocate II
Advocate II

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

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

Highlighted
Helper V
Helper V

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

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:

table positions.jpg

 

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.

Highlighted
Helper V
Helper V

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

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

url

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors