Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
90 | |
35 | |
35 |
User | Count |
---|---|
153 | |
99 | |
82 | |
63 | |
54 |