Data structure for multiple lists

[qharley]

I am very new to Power BI and currently, I am trying to make an analysis of an extensive list of standards as a part of an organizational audit that I have conducted with my team.

I work in healthcare so there are specific standard lists for inpatients, mental health, the emergency department... etc. Each set of these standards is divided into 3 levels of priorities Priority 1, 2 and 3. The audit was done in multiple locations, and these standards were marked as Met & Unmet

I am looking for the best structure for the data so that I can use Power Query and Power Pivot to highlight:

• Standards Met & Unmet in each standard list
• Standards Met & Unmet at which location
• Highlight Priority 1 & 2 in a heat map (2 colours)

This is an example of how the data is structured now 

Std_id	#	Criteria	Priority	Amb_clinic1	Amb_clinic2	Amb_clinic3	Amb_clinic4	Amb_clinic5
Amb_1.1	1.1	xxxxxxxxxxx xxxxxxxxx	3	Unmet		Met	Met	Met

Thanks,

[Icey · Accepted solution]

Hi @qharley ,

Based on my understanding of your requirements, you can try this:

1. In Power Query Editor, unpivot your columns: Amb_clinic1 to Amb_clinic5.

2. In Power BI Desktop, you can use Standard as a slicer to filter Met or Unmet.

3. In Power BI Desktop, you can set background color of Priority 1 & 2.

Hope this could help you. For more details, please check the attached PBIX file.

Reference: https://docs.microsoft.com/en-us/power-bi/desktop-what-is-desktop.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[qharley]

Thank you so much. That's what I was actually looking for.

There is another piece related to conditional formating. I have multiple standards and the example we used is only for one set of them.

What is common across different lists is that they have the same priority (1) but with different std_id. The text in the standard phrase is exactly the same but the id varies from one standard to another.

How I can draw a matrix to highlight the priority (1) if unmet with red and met with green through all location and all standard lists

[Icey]

Hi @qharley ,

Try this:

1. Create a measure.

Measure =
IF (
    MAX ( 'Table'[Priority] ) = 1,
    IF (
        MAX ( 'Table'[Standard] ) = "Met",
        1,
        IF ( MAX ( 'Table'[Standard] ) = "Unmet", 0 )
    )
)

2. Set conditional formatting.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Well, you almost certainly want to unpivot your Amb_clinic# columns.