Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello community folks!
I am trying to break project date ranges into 3 equal groups (where possible) I have a list of all dates each project was worked, and want to automatically show which 3rd a date falls into. See below
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 have looked up Rank functions, etc, but am finding nothing where i can set the max number at 3. Can anyone help me with this?
Solved! Go to Solution.
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.
Hi @Anonymous
I wouls use MOD in ths case.
Lets say you have the output of your rank function in
Var _rank = RANK(......)
Return MOD(_rank,3)+1
This will give you the output only as 1, 2 and 3
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
@pranit828 I'm sorry, I'm not following. Can you explain in more detail please?
HI @Anonymous
rank Measure =
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
@pranit828 im sorry to say that did not work.
With a live example, this is the result of your formula
Project # | Dates worked | |
Project 1 | Friday, June 26, 2020 | 2 |
Project 1 | Saturday, June 27, 2020 | 1 |
Project 1 | Sunday, June 28, 2020 | 1 |
Project 1 | Monday, June 29, 2020 | 1 |
Project 1 | Tuesday, June 30, 2020 | 3 |
Project 1 | Wednesday, July 1, 2020 | 1 |
Project 1 | Thursday, July 2, 2020 | 1 |
Project 1 | Friday, July 3, 2020 | 2 |
Project 1 | Saturday, July 4, 2020 | 2 |
Project 1 | Sunday, July 5, 2020 | 1 |
Project 1 | Monday, July 6, 2020 | 3 |
Project 1 | Tuesday, July 7, 2020 | 2 |
Project 1 | Wednesday, July 8, 2020 | 3 |
Project 1 | Thursday, July 9, 2020 | 1 |
Project 1 | Friday, July 10, 2020 | 2 |
Project 1 | Saturday, July 11, 2020 | 1 |
Project 1 | Sunday, July 12, 2020 | 1 |
Project 1 | Monday, July 13, 2020 | 1 |
Project 1 | Tuesday, July 14, 2020 | 3 |
Project 1 | Wednesday, July 15, 2020 | 1 |
Project 1 | Thursday, July 16, 2020 | 1 |
Project 1 | Friday, July 17, 2020 | 3 |
Project 1 | Saturday, July 18, 2020 | 1 |
Project 1 | Sunday, July 19, 2020 | 1 |
Project 1 | Monday, July 20, 2020 | 1 |
Project 1 | Tuesday, July 21, 2020 | 1 |
Project 1 | Wednesday, July 22, 2020 | 3 |
Project 1 | Thursday, July 23, 2020 | 3 |
Project 1 | Friday, July 24, 2020 | 2 |
Project 1 | Saturday, July 25, 2020 | 3 |
Project 1 | Sunday, July 26, 2020 | 3 |
Project 1 | Monday, July 27, 2020 | 3 |
the result im looking for is below
Project # | Dates worked | |
Project 1 | Friday, June 26, 2020 | 1 |
Project 1 | Saturday, June 27, 2020 | 1 |
Project 1 | Sunday, June 28, 2020 | 1 |
Project 1 | Monday, June 29, 2020 | 1 |
Project 1 | Tuesday, June 30, 2020 | 1 |
Project 1 | Wednesday, July 1, 2020 | 1 |
Project 1 | Thursday, July 2, 2020 | 1 |
Project 1 | Friday, July 3, 2020 | 1 |
Project 1 | Saturday, July 4, 2020 | 1 |
Project 1 | Sunday, July 5, 2020 | 1 |
Project 1 | Monday, July 6, 2020 | 1 |
Project 1 | Tuesday, July 7, 2020 | 2 |
Project 1 | Wednesday, July 8, 2020 | 2 |
Project 1 | Thursday, July 9, 2020 | 2 |
Project 1 | Friday, July 10, 2020 | 2 |
Project 1 | Saturday, July 11, 2020 | 2 |
Project 1 | Sunday, July 12, 2020 | 2 |
Project 1 | Monday, July 13, 2020 | 2 |
Project 1 | Tuesday, July 14, 2020 | 2 |
Project 1 | Wednesday, July 15, 2020 | 2 |
Project 1 | Thursday, July 16, 2020 | 2 |
Project 1 | Friday, July 17, 2020 | 2 |
Project 1 | Saturday, July 18, 2020 | 3 |
Project 1 | Sunday, July 19, 2020 | 3 |
Project 1 | Monday, July 20, 2020 | 3 |
Project 1 | Tuesday, July 21, 2020 | 3 |
Project 1 | Wednesday, July 22, 2020 | 3 |
Project 1 | Thursday, July 23, 2020 | 3 |
Project 1 | Friday, July 24, 2020 | 3 |
Project 1 | Saturday, July 25, 2020 | 3 |
Project 1 | Sunday, July 26, 2020 | 3 |
Project 1 | Monday, July 27, 2020 | 3 |
Im trying to break the Projects duration into 3 parts... in chronological order. So in the case above, the first 11 dates would be "1", the middle "2", and the latter "3"
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)
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
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)
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
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.
@Anonymous Sorry, not following this completely. Are you showing us sample source data, expected output, both? If this is sample source data can you post expected output and the logic behind how you want the transformation to happen?
Are you simply looking for the MAX number per project in the last column? You could do that via Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
In your case,
Measure =
VAR __Project = MAX([Project])
VAR __MaxStatus = MAXX(FILTER('Table',[Project]=__Project),[Status])
RETURN
__MaxStatus
Sorry about that @Greg_Deckler I showed the final output i was going for.
I have a list of projects and all the dates those projects were worked on. shown here.
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 |
What i need to do is break each set of dates, by project, up to a max of 3 groups.(essentially splitting the project duration into 3rds) The end result would look like this.
Project # | Dates worked | |
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 |
Does this help?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |