Forum Discussion
Help needed with dynamic Max axis calculation measure.
Hello All,
I have a measure that should calculate the max value for the primary and secondary Y axix in a Line stacked column chart. It works well as long as I am trying to slice the chart with the Time_To_Resolve field parameter. However when I am filtering on anything else, the Max axis doesn't get adjusted, and if the max value is higher than the calculated Max, it goes off the chart.
Any help would be very much appreciated.
Her is the code:
TTR_Primary_Axis =
VAR Metric = SELECTEDVALUE(Time_To_Resolve[Time_To_Resolve Order])
VAR MaxValue =
MAXX(
ADDCOLUMNS(
SUMMARIZE(
ALLSELECTED(Jira_SLA_Report),
Jira_SLA_Report[Resolved_Year],
Jira_SLA_Report[Resolved_Month]
),
"Value", SWITCH(
TRUE(),
Metric = 0, [Average_Resolution_Time],
Metric = 1, [Median_Resolution_Time],
Metric = 2, [Percentile_Resolution_Time]
)
),
[Value]
)
RETURN
MaxValueHi VulnBi,
Thank you for reaching out to the Microsoft fabric community forum. Thank you pankajnamekar25, for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.
For creating column name TTR_Primary_Axis_Fixed in Jira_SLA_Report Table:
TTR_Primary_Axis_Fixed =VAR Metric = SELECTEDVALUE(Time_To_Resolve[Time_To_Resolve Order])
VAR MaxValue =
MAXX(
ADDCOLUMNS(
SUMMARIZE(
ALL(Jira_SLA_Report),
Jira_SLA_Report[Resolved_Year],
Jira_SLA_Report[Resolved_Month]
),
"Value",
SWITCH(
TRUE(),
Metric = 0, AVERAGE(Jira_SLA_Report[Average_Resolution_Time]),
Metric = 1, AVERAGE(Jira_SLA_Report[Median_Resolution_Time]),
Metric = 2, AVERAGE(Jira_SLA_Report[Percentile_Resolution_Time])
)
),
[Value]
)
RETURN
MaxValue
I am also including .pbix file for your better understanding, please have a look into it:
I hope this could resolve your issue, if you need any further assistance, feel free to reach out. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
6 Replies
Hello VulnBi
Try this measure
TTR_Primary_Axis =
VAR Metric = SELECTEDVALUE(Time_To_Resolve[Time_To_Resolve Order])
VAR MaxValue =
MAXX(
ADDCOLUMNS(
SUMMARIZE(
CALCULATETABLE(
Jira_SLA_Report,
KEEPFILTERS(Jira_SLA_Report[Resolved_Year]),
KEEPFILTERS(Jira_SLA_Report[Resolved_Month])
),
Jira_SLA_Report[Resolved_Year],
Jira_SLA_Report[Resolved_Month]
),
"Value", SWITCH(
TRUE(),
Metric = 0, [Average_Resolution_Time],
Metric = 1, [Median_Resolution_Time],
Metric = 2, [Percentile_Resolution_Time]
)
),
[Value]
)
RETURN
MaxValueThanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
Thank you for your help!
Sadly I am getting the following error in the visual:I managed to fix it by adding KEEPFILTERS(VALUES(Jira_SLA_Report[Resolved_Year])),
But the outcome is still the same.
Hi VulnBi,
Thank you for reaching out to the Microsoft fabric community forum. Thank you pankajnamekar25, for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.
For creating column name TTR_Primary_Axis_Fixed in Jira_SLA_Report Table:
TTR_Primary_Axis_Fixed =VAR Metric = SELECTEDVALUE(Time_To_Resolve[Time_To_Resolve Order])
VAR MaxValue =
MAXX(
ADDCOLUMNS(
SUMMARIZE(
ALL(Jira_SLA_Report),
Jira_SLA_Report[Resolved_Year],
Jira_SLA_Report[Resolved_Month]
),
"Value",
SWITCH(
TRUE(),
Metric = 0, AVERAGE(Jira_SLA_Report[Average_Resolution_Time]),
Metric = 1, AVERAGE(Jira_SLA_Report[Median_Resolution_Time]),
Metric = 2, AVERAGE(Jira_SLA_Report[Percentile_Resolution_Time])
)
),
[Value]
)
RETURN
MaxValue
I am also including .pbix file for your better understanding, please have a look into it:
I hope this could resolve your issue, if you need any further assistance, feel free to reach out. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.Hi VulnBi,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi VulnBi,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
