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.
Hi
Apologies if this is a simple fix but I cant seem to figure this out.
I have a table 'SOR' that lists tasks assigned to individuals. I want to create a stacked chart to show the top 10 individuals with assigned tasks with various status displayed in the stack. I need to exclude the completed tasks so only outstanding tasks are shown.
I have created a chart without the Status filter with a RANKX measure and this works fine
However I run into trouble when adding the Status column as the Legend as this trashes the RANKX function:
I have tried various things to resolve this such as placing a filter on the RANX function:
Solved! Go to Solution.
The problem is your ranking calculation is being evaluated within the context of the status (applied via the legend). You need to remove that filter inside the ranking calculation.
Top Responsible =
RANKX(
ALL( SOR[Person Responsible] ),
CALCULATE(
COUNT( SOR[Person Responsible] ),
FILTER(
ALL(SOR[Status]),
SOR[Status] <> "Completed"
)
)
)
The above is hard coded to disregard "Completed" status. You could make this more dynamic and apply that filter via the filter pane and use ALLSELECTED...
Top Responsible =
RANKX(
ALL( SOR[Person Responsible] ),
CALCULATE(
COUNT( SOR[Person Responsible] ),
ALLSELECTED(SOR[Status])
)
)
...this would allow users to choose which statuses they want to disregard.
The problem is your ranking calculation is being evaluated within the context of the status (applied via the legend). You need to remove that filter inside the ranking calculation.
Top Responsible =
RANKX(
ALL( SOR[Person Responsible] ),
CALCULATE(
COUNT( SOR[Person Responsible] ),
FILTER(
ALL(SOR[Status]),
SOR[Status] <> "Completed"
)
)
)
The above is hard coded to disregard "Completed" status. You could make this more dynamic and apply that filter via the filter pane and use ALLSELECTED...
Top Responsible =
RANKX(
ALL( SOR[Person Responsible] ),
CALCULATE(
COUNT( SOR[Person Responsible] ),
ALLSELECTED(SOR[Status])
)
)
...this would allow users to choose which statuses they want to disregard.
Thank you, that has done the trick, you are a legend!!! (pun intended 😉 )
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 |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |