Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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
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
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
Thank you to both of you for all your help
It worked - Much appreciated