Really need your help

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

Need Help

v-frfei-msft

7 years ago

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()))

For more details, please check the pbix as attached.

Regards,

Frank

re.pbix35 KB

GilbertQ
7 years ago
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.

Here is a link to the PBIX: Really need help

Forum Discussion

Really need your help

8 Replies