Skills Advisor | Data Visualization

Power BI Organization Rank Visualization Guide

This guide addresses a Power BI data visualization issue where users aim to display a specific organization and its ranked adjacent organizations, focusing on effective measures and filtering to enhance clarity and usability in the chart.

Prompt

i have a chart that shows 42 organisations however i want my organisation to be shown automatically without dragging the chart slicer to my organisation position. the first dax measure used is 1)Opido1_ICB = CALCULATE(
    SUM(Fact_Opiod1_ICB[Patients receiving opioid pain medicines per 1,000 patients]),
   
      
    DatesCalendar[MonthnYear]) second dax measure is OrgRank = RANKX(
    ALL(DIM_Region[Prescribing Organisation]),
    [Opido1_ICB],
    ,
    DESC
) the third dax measure is IsMyOrganization = IF(
    SELECTEDVALUE('DIM_Region'[Prescribing Organisation]) = "NHS Bath And North East Somerset, Swindon And Wiltshire Integrated Care Board",
    1,
    0
) which i use to highlight my organisation in green and the last dax measure that i want to use as a filter in my visual chart is IsVisible = 
VAR CurrentRank = [OrgRank]
VAR MinRank = MAX(CurrentRank - 5, 1)    // Show 5 before
VAR MaxRank = CurrentRank + 4            // Show 5 after
RETURN
IF(
    [OrgRank] >= MinRank && [OrgRank] <= MaxRank,
    1,
    0
) however when i use ISvisible in the filter pane while adding prescribing organisation in x axis and opiod1 icb in y axis my chart still shows all 42 organisations and do not shows 5 before and 5 after my organisation. i need to resolve it to avoid user and myself dragging the chart slicer to the position of my organisation

Answer

Data Visualization Challenge Overview

You are encountering an issue with a data visualization chart in Power BI, where you want to automatically display a specific organization along with its adjacent organizations based on a defined rank. The following outlines the current measures and steps to resolve the issue.

Current Measures Overview

Opido1_ICB Measure
This measure calculates the sum of patients receiving opioid pain medications per 1,000 patients.

DAX:

Opido1_ICB = CALCULATE(
    SUM(Fact_Opiod1_ICB[Patients receiving opioid pain medicines per 1,000 patients]),
    DatesCalendar[MonthnYear]
)

OrgRank Measure
This measure assigns a rank to organizations based on the Opido1_ICB measure.

DAX:

OrgRank = RANKX(
    ALL(DIM_Region[Prescribing Organisation]),
    [Opido1_ICB],
    ,
    DESC
)

IsMyOrganization Measure
This measure checks if the selected organization is "NHS Bath And North East Somerset, Swindon And Wiltshire Integrated Care Board".

DAX:

IsMyOrganization = IF(
    SELECTEDVALUE('DIM_Region'[Prescribing Organisation]) = "NHS Bath And North East Somerset, Swindon And Wiltshire Integrated Care Board",
    1,
    0
)

IsVisible Measure
This measure is designed to filter the chart to show the organization itself plus five organizations before and after it in rank.

DAX:

IsVisible = 
VAR CurrentRank = [OrgRank]
VAR MinRank = MAX(CurrentRank - 5, 1)    // Show 5 before
VAR MaxRank = CurrentRank + 4            // Show 5 after
RETURN
IF(
    [OrgRank] >= MinRank && [OrgRank] <= MaxRank,
    1,
    0
)

Solution Approach

To effectively apply the IsVisible filter, you should ensure that you are correctly utilizing this measure within your visual without showing all 42 organizations. Follow these structured steps:

1. Update Visual Filter

In the visualization pane, go to the Filters section for the chart you are working with.
Drag the IsVisible measure into the "Filters on this visual" area.
Set the filter condition to show only values where IsVisible = 1. This will restrict the visual to display only the organizations that meet your defined visibility criteria.

2. Ensure Correct Data Context

Ensure that your visual retains the correct filtering context. The IsVisible measure must evaluate the rank based on the selections in the visual.

Use a slicer for the Prescribing Organisation as a filter in the report view.
Ensure that other visuals do not filter out the organization you want to focus on.

3. Check RANKX Calculation

Ensure there are no conflicting filters affecting how the OrgRank is calculated. Using ALL removes all filters from the Prescribing Organisation, which could affect the visibility of your specific organization and its ranking.

Consider using something like ALLEXCEPT if you want to maintain context from a previous filter apart from the Prescribing Organisation.

Example of OrgRank Adjusted:

OrgRank = RANKX(
    ALLEXCEPT(DIM_Region, DIM_Region[SomeOtherContexts]),
    [Opido1_ICB],
    ,
    DESC
)

Conclusion

By ensuring you apply the IsVisible filter correctly and maintain the necessary context for your rank calculations, you will achieve the desired result of focusing your data visualization on your organization with its surrounding ranks.

Utilize these insights and adjustments to elevate your understanding of data visualizations in Power BI. For further exploration, consider leveraging the Enterprise DNA Platform courses that delve deeper into filtering techniques and advanced DAX measures.