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.
Kelly
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 |
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 |
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 |
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 |
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.
Kelly
@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@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)
DAX is easy, CALCULATE makes DAX hard...
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
