Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |