March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello,
My requirement is to pull three years of data from today, the ask is to display length of stay over this three years of data by month (YYYYMM) in a line chart. In addition, add MEDIAN length of stay as a dash line, the calculation for MEDIAN is only take median of the first 10 starting months of data as circled on 2nd table screenshot.
To do this, I created a RANK column below as shown on the above table:
Solved! Go to Solution.
Hi @Buidoistreet ,
Please create a measure as below to work on it instead of using a calculated column.
Measure =
VAR mindate =
CALCULATE ( MIN ( 'ERAS'[date] ), ALLSELECTED ( ERAS ) )
VAR maxdate =
EDATE ( mindate, 10 )
RETURN
CALCULATE (
AVERAGE ( ERAS[Length_of_Stay] ),
FILTER ( ALL ( ERAS ), 'ERAS'[date] >= mindate && 'ERAS'[date] < maxdate )
)
For more details, please check the pbix as attached.
Hi @Buidoistreet ,
Please create a measure as below to work on it instead of using a calculated column.
Measure =
VAR mindate =
CALCULATE ( MIN ( 'ERAS'[date] ), ALLSELECTED ( ERAS ) )
VAR maxdate =
EDATE ( mindate, 10 )
RETURN
CALCULATE (
AVERAGE ( ERAS[Length_of_Stay] ),
FILTER ( ALL ( ERAS ), 'ERAS'[date] >= mindate && 'ERAS'[date] < maxdate )
)
For more details, please check the pbix as attached.
That helps. Thanks so much!
See if this way Rank can help
new Rank =
var _min = mix(allselected(ERAS),[Rank])
return
[Rank]-_min +1
Thanks atmitchandak for a quick reply!
I created a new rank as column and I've received this error "The expression refers to mutiple columns. Multiple columns cannot be converted to scalar value."
new Rank =
var _min = min(allselected(ERAS), ERAS[Rank])
return
ERAS[Rank]-_min +1
Please advice. Thanks!
Oh, there was a typo in MINX, it should be
new Rank =
var _min = minx(allselected(ERAS), ERAS[Rank])
return
ERAS[Rank]-_min +1
However, the new rank value still look same as the old rank. It doens't dynamically re-rank.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
129 | |
90 | |
75 | |
58 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |