cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ham2889 Frequent Visitor
Frequent Visitor

Really need your help

Hi

I am really struggling with this It is regarding temporary contracts

 

I have a list of IDs in a column called "IDs" In this column there are many duplicates as the same ID could have worked over 8 years, generally for 3 months at a time.

 

Many of the IDs have worked numerous times in one years causing numerous duplicates for that year.

 

I have a column called years which picks up the year in which the work took place. So as said I could have the same year appear more than once for the ID.

 

What I am trying to work out is: How many of these temporary contractors have been working for us for 4 years in a row (so picking up years 2016,2017,2018,2019 from the years column against an individual ID.

 

 

Any help you can give would be so much help Thank you

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Really need your help

Hi @ham2889,

 

One sample for your refernce.

 

1. Create a calculated table as below. Here we needn't create relationship between the new table and the fact table.

 

Year = VALUES(Table1[Year])

2. Create the measures as below.

 

Measure = 
var _count = CALCULATE(DISTINCTCOUNT(Table1[Year]),ALLSELECTED(Table1),VALUES(Table1[IDs]))
var _dis = CALCULATE(DISTINCTCOUNT('Year'[Year]))
return
IF(MAX(Table1[Year]) in VALUES('Year'[Year]),CALCULATE(DISTINCTCOUNT(Table1[IDs]),FILTER(ALLSELECTED(Table1[Year]),_count=_dis)))
Measure 2 = CALCULATE(DISTINCTCOUNT(Table1[IDs]),FILTER(Table1,[Measure]<>BLANK()))

Capture.PNG

For more details, please check the pbix as attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Super User
Super User

Re: Really need your help

Hi @ham2889

 

Below is my solution where I created a very small star schema. This is ideal when creating data models and can easily solve your issue as shown below.

 

@v-frfei-msft solution too works, so you can decide which one works for you.

 

image.png

Here is a link to the PBIX: Really need help


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Really need your help

Hi there

What I would do is to take all the ID's and put them into a separate distinct table.

I would then create a relationship between the ID's in the two tables.

Once that is done you can then use the Dates from your main table and drag in the ID's from your ID table, which will then show you the ID's and for which years they worked?

Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog
ham2889 Frequent Visitor
Frequent Visitor

Re: Really need your help

Thank you for responding. Yes I have tried this. I can create the distinct IDs however how do I match them up with the main ID table.
Sorry I am quite new and dont know how to drag.
Is there a formula you need to write ?
Super User
Super User

Re: Really need your help

Hi @ham2889

 

Please have a look at this below on how to create the relationships

 

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog
ham2889 Frequent Visitor
Frequent Visitor

Re: Really need your help

Thanks once again. Sorry I didn't explain it well

This is an example of my main table. In the real table I have over 10,000 IDs

IDs. Year
101. 2016
102. 2016
103. 2016
102. 2018
101. 2017
103. 2019
102. 2018
102. 2018
102. 2017
103. 2016
102. 2019
101. 2019
101. 2019

This is an example of the table, but I would like to find out how many IDs have worked continually for 4 years.
Looking at this short list it's only ID. 102 as he has worked at least once in 2016,2017,2018 and 2019 where all the others there is a gap.

I created a new table as you said and took away all the duplicate iDs and built a relationship with the main table. This when I add this to a table I can then drag a column for the years and it shows how many times they have worked over the years

However I want to create a column that shows a 1 against those IDs that have only worked continually for 4 years (so in this case only 102) so I would presumably need to write a formula but I have tried all sorts but as I have duplicates of IDs and years it never works.

I hope this is clearer and thanks for your help.
Community Support Team
Community Support Team

Re: Really need your help

Hi @ham2889,

 

One sample for your refernce.

 

1. Create a calculated table as below. Here we needn't create relationship between the new table and the fact table.

 

Year = VALUES(Table1[Year])

2. Create the measures as below.

 

Measure = 
var _count = CALCULATE(DISTINCTCOUNT(Table1[Year]),ALLSELECTED(Table1),VALUES(Table1[IDs]))
var _dis = CALCULATE(DISTINCTCOUNT('Year'[Year]))
return
IF(MAX(Table1[Year]) in VALUES('Year'[Year]),CALCULATE(DISTINCTCOUNT(Table1[IDs]),FILTER(ALLSELECTED(Table1[Year]),_count=_dis)))
Measure 2 = CALCULATE(DISTINCTCOUNT(Table1[IDs]),FILTER(Table1,[Measure]<>BLANK()))

Capture.PNG

For more details, please check the pbix as attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Super User
Super User

Re: Really need your help

Hi @ham2889

 

Below is my solution where I created a very small star schema. This is ideal when creating data models and can easily solve your issue as shown below.

 

@v-frfei-msft solution too works, so you can decide which one works for you.

 

image.png

Here is a link to the PBIX: Really need help


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog

View solution in original post

ham2889 Frequent Visitor
Frequent Visitor

Re: Really need your help

Thank you to both of you for all your help

 

It worked - Much appreciated

 

Super User
Super User

Re: Really need your help

Pleasure, glad you got a working version.

Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 46 members 823 guests
Please welcome our newest community members: