Skills Advisor | Data Visualization

Dynamic Data Visualization for Organization Focus

This guide outlines how to enhance DAX measures for a bar chart to automatically prioritize "NHS Bath And North East Somerset, Swindon And Wiltshire Integrated Care Board." It details a dynamic sorting mechanism ensuring visibility while

Prompt

i still dont understand the chart still does no drag automatically to NHS BATH organisation as below, i still need to drag to the organisation on the chart? please not i do not have a filter to select the organisation, i want the chart to show 42 organisations but still focus on organisation A position visible, the chart is sorted by highest value to lower values  Opido1_ICB_Combined = 
VAR SelectedOrg = "NHS Bath And North East Somerset, Swindon And Wiltshire Integrated Care Board"
VAR BaseMeasure = SUM(Fact_Opiod1_ICB[Patients receiving opioid pain medicines per 1,000 patients])
VAR TotalMeasure =
    CALCULATE(
        BaseMeasure,
        ALL(DatesCalendar[MonthnYear])
    )
VAR OrgContext =
    UNION(
        FILTER(
            VALUES(Fact_Opiod1_ICB[Prescribing organisation]),
            Fact_Opiod1_ICB[Prescribing organisation] = SelectedOrg
        ),
        FILTER(
            VALUES(Fact_Opiod1_ICB[Prescribing organisation]),
            Fact_Opiod1_ICB[Prescribing organisation] <> SelectedOrg
        )
    )

RETURN
    IF(
        SELECTEDVALUE(Fact_Opiod1_ICB[Prescribing organisation]) IN OrgContext,
        CALCULATE(BaseMeasure, OrgContext),
        TotalMeasure
    )

Answer

Data Visualization Guidance: Automatically Focusing on Specific Organizations

Problem Analysis

The goal is to display a bar chart showing 42 organizations with a specific focus on "NHS Bath And North East Somerset, Swindon And Wiltshire Integrated Care Board" (hereafter referred to as "SelectedOrg"). You require the chart to showcase all organizations while ensuring that the SelectedOrg's position is prominently visible, automatically adjusting as required without manual dragging.

Proposed Solution

To achieve the desired effect in your DAX measure and improve the chart usability, consider the following enhancements:

Ensure Focus on Selected Organization: Modify the chart to always include the SelectedOrg at the top.
Set Up a Hierarchical Sort: Implement a sorting mechanism that prioritizes the SelectedOrg without the need for extra filters.
Use of Measures: Utilize measures to control the rendering of the results dynamically based on the organization context.

Revised DAX Measure

You can adjust your DAX measure to ensure that the chart correctly positions the SelectedOrg while still displaying all organizations. The idea is to apply a conditional sorting mechanism based on your requirements.

Here’s the modified DAX code:

Opido1_ICB_Combined = 
VAR SelectedOrg = "NHS Bath And North East Somerset, Swindon And Wiltshire Integrated Care Board"
VAR BaseMeasure = SUM(Fact_Opiod1_ICB[Patients receiving opioid pain medicines per 1,000 patients])
VAR TotalMeasure =
    CALCULATE(
        BaseMeasure,
        ALL(DatesCalendar[MonthnYear])
    )
VAR OrgContext =
    VALUES(Fact_Opiod1_ICB[Prescribing organisation])

RETURN
    IF(
        SELECTEDVALUE(Fact_Opiod1_ICB[Prescribing organisation]) = SelectedOrg,
        BaseMeasure,
        CALCULATE(BaseMeasure, OrgContext)
    )

Explanation of Adjustments

Removal of UNION: The revised measure utilizes a straightforward VALUES function to gather the organization context, removing unnecessary complexity.
Condition for Selected Org: We directly check if the selected organization is equal to SelectedOrg to prioritize its visibility.
Dynamic Calculation: The total measure is computed in a context that considers all the organizations, ensuring that the sorting will always reflect the performance accurately.

Best Practices

Avoid Hardcoding: Consider parameterizing SelectedOrg to allow flexibility in case the focus organization changes.
Sorting in Reports: Ensure that the chart configuration in the visualization tool is set to sort by the measure result, allowing SelectedOrg to be placed at the top.
Testing: After modifying the measure, thoroughly test it across different scenarios to validate that the SelectedOrg remains prioritized.

Conclusion

Implementing the revised DAX measure and following the best practices will enhance the visibility of the "NHS Bath" organization in your chart, fulfilling your goal effectively. By automating this process, you will minimize the manual adjustments needed, thereby improving usability and data interpretation.

For further learning and advanced techniques, consider exploring courses available on the Enterprise DNA Platform.