Forum Discussion
Distribute date ranges into groups automatically
Hi Anonymous ，
First create a column as below:
Rank = RANKX(FILTER('Table','Table'[Project #]=EARLIER('Table'[Project #])),'Table'[Dates worked],,ASC)Then create a measure as below:
Thirds = var _max=MAXX(FILTER(ALL('Table'),'Table'[Project #]=MAX('Table'[Project #])),'Table'[Rank]) var _count=CALCULATE(COUNT('Table'[Project #]),FILTER(ALL('Table'),'Table'[Project #]=MAX('Table'[Project #]))) Return IF(_max<=3,MAX('Table'[Rank]),DIVIDE(MAX('Table'[Rank]),_count/3))And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Hi Anonymous
_number =
var _cnt = rankx(allselected(Table1),CALCULATE ( MAX (Table1[Dates worked])),,ASC)
var _max = CALCULATE(COUNTROWS(Table1),ALLEXCEPT(table1,Table1[Project #]))
var _d3 = _max/3
return CEILING(_cnt/_d3,1)Anonymous
So as a measure, it doesn't seem to stop at 3. I see values up into the hundreds.
because of the insight im looking to get, the best solution for this would be a calculated column..however when i add the formula in for my new column I get the error "A circular dependency was detected: Table1[Column]."
any thoughts?
Hi Anonymous
According to your sample my previous reply answers your question.
Probably you should put more time in making a good sample of your data when ask so that you get to the point reply.
Try the below as a measure, column is preferred when there is a calculation done for each row.
_number = var _cnt = rankx(allselected(Table1),CALCULATE ( MAX (Table1[Dates worked]), allexcept(table1, table1[Project #])),,ASC) var _max = CALCULATE(COUNTROWS(Table1),ALLEXCEPT(table1,Table1[Project #])) var _d3 = _max/3 return CEILING(_cnt/_d3,1)My apologies that my request was unclear. I don't use the forums often to request help, so forgive me for thinking I had explained what i was looking for thoroughly enough.
I have tried your measure, and its actually gotten worse in the number return.
I shall try to explain this more in depth.
The need: To break down a Projects duration (start to finish) into thirds. Identify which dates fall into which 3rd of each project. Durations can be anywhere from 1 day to hundreds of days long. We have a table with thousands of projects of varying durations. I have already created a table that shows the Project Number and each individual date the project was active. shown here
Example Table:
Project # Dates worked Project 1 7/4/2019 Project 1 7/5/2019 Project 1 7/6/2019 Project 1 7/7/2019 Project 1 7/8/2019 Project 1 7/9/2019 Project 2 8/10/2019 Project 2 8/11/2019 Project 3 5/4/2019 Project 3 5/5/2019 Project 3 5/6/2019 The request: Can anyone help me create a Calculated Column, either in DAX or in Power Query, to label each row as either the first, middle or latter third of a project. The result would look like below
Example Table 2: Desired Outcome
Project # Dates worked Thirds Project 1 7/4/2019 1 Project 1 7/5/2019 1 Project 1 7/6/2019 2 Project 1 7/7/2019 2 Project 1 7/8/2019 3 Project 1 7/9/2019 3 Project 2 8/10/2019 1 Project 2 8/11/2019 2 Project 3 5/4/2019 1 Project 3 5/5/2019 2 Project 3 5/6/2019 3 I hope this explanation is clearer than my original ask.
Hi Anonymous ，
First create a column as below:
Rank = RANKX(FILTER('Table','Table'[Project #]=EARLIER('Table'[Project #])),'Table'[Dates worked],,ASC)Then create a measure as below:
Thirds = var _max=MAXX(FILTER(ALL('Table'),'Table'[Project #]=MAX('Table'[Project #])),'Table'[Rank]) var _count=CALCULATE(COUNT('Table'[Project #]),FILTER(ALL('Table'),'Table'[Project #]=MAX('Table'[Project #]))) Return IF(_max<=3,MAX('Table'[Rank]),DIVIDE(MAX('Table'[Rank]),_count/3))And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!