The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Dear all,
I need your help in visualizing SLA status on pie chart, where i want to show number of task thats "Breached or Complaint ".
The scenario is I have multiple dates from one record and my current date filter when filtered will show the tasks even if one date in a task falls in the date selection. My sample data is as shown below:
Task Name | Planned Date | Planned End Date | Start date | End Date | Initial Date | final Date | SLA Status |
Task1 | 1-Mar-24 | 5-Mar-24 | 1-Mar-24 | 7-Mar-24 | 9-Mar-24 | 11-Mar-24 | Breached |
Task2 | 1-Feb-24 | 5-Feb-24 | 2-Feb-24 | 7-Feb-24 | 9-Mar-24 | 11-Mar-24 | Complaint |
Task3 | 1-Jan-24 | 5-Jan-24 | 5-Feb-24 | 15-Feb-24 | 9-Mar-24 | 11-Mar-24 | Breached |
Task4 | 1-Jan-24 | 10-Jan-24 | 2-Jan-24 | 7-Jan-24 | 9-Mar-24 | 11-Mar-24 | Complaint |
Now my issue is when i apply date filter to show last 2 months data, the visual shows breach count is 2 and Complaint count is 2 which shouldnt be the case. It should show complaint = 1 and breached = 2 as i want the SLA display to be only based on Start and End date and task 4 does not fall in the date range category eventhough it has current date in the task. SLA is calculated based on Start and End date .Kindly help as i am not able to figure out a way around this problem.
Thanks,
Hello @sizi,
Can you please try the following DAX:
Breached Count =
CALCULATE(
COUNTROWS('YourTable'),
'YourTable'[SLA Status] = "Breached",
NOT(ISBLANK('YourTable'[Start date])),
NOT(ISBLANK('YourTable'[End Date])),
USERELATIONSHIP('YourTable'[Start date], 'CalendarTable'[Date]),
USERELATIONSHIP('YourTable'[End Date], 'CalendarTable'[Date])
)
Compliant Count =
CALCULATE(
COUNTROWS('YourTable'),
'YourTable'[SLA Status] = "Complaint",
NOT(ISBLANK('YourTable'[Start date])),
NOT(ISBLANK('YourTable'[End Date])),
USERELATIONSHIP('YourTable'[Start date], 'CalendarTable'[Date]),
USERELATIONSHIP('YourTable'[End Date], 'CalendarTable'[Date])
)
Hope this helps!
Hi Sahir,
Thank you for looking into this. However the dax is giving me error as below:
USERELATIONSHIP function can only use the two columns references participating in relationship.
Please note my date filter is defined to consider any single date available in the task and i want sla to show the records only if start and end date is within the date filter selected even if the task has recent date which falls under date filter range, it should exclude.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
248 | |
123 | |
111 | |
78 | |
74 |