Forum Discussion
Rankx all return 1
Anonymous CALCULATE() converts a row context to a filter context. Without it the row context at each step in the iteration doesn't work properly with the filter context coming from the ALLSELECTED(). You get an implicit CALCULATE when you reference an existing measure like this:
Total Sales = SUM(sales[sales amount])
Sales Rank = RANKX (ALLSELECTED(sales[employee number]), [Total Sales])
...but when you spell out the expression instead of using a measure you also have to spell out the invisible CALCULATE yourself. The above formula is identical to your
Sales Rank = RANKX( ALLSELECTED(sales[employee number]), CALCULATE( SUM(sales[sales amount]))
This article might help to explain it.
Anonymous CALCULATE() converts a row context to a filter context. Without it the row context at each step in the iteration doesn't work properly with the filter context coming from the ALLSELECTED(). You get an implicit CALCULATE when you reference an existing measure like this:
Total Sales = SUM(sales[sales amount])
Sales Rank = RANKX (ALLSELECTED(sales[employee number]), [Total Sales])
...but when you spell out the expression instead of using a measure you also have to spell out the invisible CALCULATE yourself. The above formula is identical to your
Sales Rank = RANKX( ALLSELECTED(sales[employee number]), CALCULATE( SUM(sales[sales amount]))
This article might help to explain it.
I am trying the same stuff but instead of SUM I am trying to get Rank by order of the date but it returns all 1s
Rank = RANKX(ALLSELECTED(Query1[UserName]),CALCULATE(MAX(Query1[StartDate])))
Would appreciate any wisdom here..
PowerBIArtist
did you enter this as a measure or a column?OK I did some testing with my own data. That ALLSELECTED thing was a specific case for the other person's presentation of data. If you're just going to drop a big list of people and rank them by date, just use ALL(Query1) instead of ALLSELCTED(Query1[UserName]). ALLSELECTED forces each user to be their own set of data to be ranked individually against themselves instead of everyone else, so among all the users that are that one user, they are all rank 1.
I still do not understand how does it work. I want to rank project name base on project value.
I have rankx=rankx(All(Merge1[Project Name]),sum(Merge1[TOtal project construction Value]))
but rank returns 1
Please help