Imitate a checklist in PowerBI

[TestAccount321]

Hi, I am trying to imitate a checklist in a PowerBI visual. I have a list of options and I want all of these options displayed but only a subset of those options highlighted. I think either a table or matrix would work but I have issues with both

Something like this is the desired final outcome

Store	Recipe	Ingredients
Albany	Chocolate Chunk	Milk (highlighted)
		Chocolate (highlighted)
		Sugar (unhighlighted)
Annapolis	Superman	Milk (highlighted)
		Chocolate (unhighlighted)
		Sugar (highlighted)

Table

If I use a table, which is good for conditionally formatting, I run into an issue where I would display the values of Store and Recipe multiple times i.e. Which is not what I want

Store	Recipe	Ingredients
Albany	Chocolate Chunk	Milk (highlighted)
Albany	Chocolate Chunk	Chocolate (highlighted)
Albany	Chocolate Chunk	Sugar (unhighlighted)
Annapolis	Superman	Milk (highlighted)
Annapolis	Superman	Chocolate (unhighlighted)
Annapolis	Superman	Sugar (highlighted)

Matrix

If I use a matrix, I can get the ingredients all on their own row with the Store and Recipe as merged columns so their values only show once. The issue with a matrix is that you cannot conditionally format rows or columns, only values. I can't make the ingredients a value as those are automatically aggregated so it would only show the first ingredient instead of them all.

My gut says it has to be done in a table but I can't get around the merging cell issue. Any help would be appreciated, I'm pretty new to PowerBI

[VahidDM · Accepted solution]

Hi @TestAccount321 

To create a checklist-like visual in Power BI where you display all options (ingredients) with certain items highlighted, and avoid repeating the Store and Recipe values, you can use a Table visual with measures to simulate merged cells and apply conditional formatting. Here's how you can achieve your desired output:

Steps:

1. Add an Index Column in Power Query:
• In Power Query Editor, add an index column to your data to uniquely identify each row.
• Go to Add Column > Index Column > From 1.
• This creates a new column, e.g., RowID.
2. Create Calculated Columns to Display Store and Recipe Once:
• In your data model, create two calculated columns to display the Store and Recipe only on the first occurrence:

IsFirstRowInGroup =

VAR CurrentStore = 'YourTable'[Store]

VAR CurrentRecipe = 'YourTable'[Recipe]

VAR CurrentRowID = 'YourTable'[RowID]

VAR PreviousRowID = CurrentRowID - 1

VAR PreviousStore = LOOKUPVALUE('YourTable'[Store], 'YourTable'[RowID], PreviousRowID)

VAR PreviousRecipe = LOOKUPVALUE('YourTable'[Recipe], 'YourTable'[RowID], PreviousRowID)

RETURN

    IF(

        PreviousStore <> CurrentStore || PreviousRecipe <> CurrentRecipe || ISBLANK(PreviousStore),

        1,

        0

    )

• Then, create display columns:

StoreDisplay =

IF('YourTable'[IsFirstRowInGroup] = 1, 'YourTable'[Store], BLANK())

RecipeDisplay =

IF('YourTable'[IsFirstRowInGroup] = 1, 'YourTable'[Recipe], BLANK())

1. Set Up the Table Visual:
• Insert a Table visual onto your report canvas.
• Add the following fields to the table:
• Columns:
• StoreDisplay
• RecipeDisplay
• Ingredient
• Sort the table by Store, then by Recipe, and then by Ingredient to ensure correct grouping.
2. Apply Conditional Formatting to Ingredients:
• If you have a column like HighlightFlag (1 for highlighted, 0 for not highlighted), use it to apply conditional formatting.
• Select the Ingredient column in the table visual.
• Go to the Visualizations pane > Format > Cell elements (or Conditional formatting in older versions).
• Choose Background color or Font color.
• Set Format by to Rules.
• Apply rules based on the HighlightFlag:
• If HighlightFlag equals 1, set your desired highlight color.
• If HighlightFlag equals 0, set a different color or leave it as default.
3. Adjust Table Formatting to Simulate Merged Cells:
• In the table visual formatting options:
• Grid:
• Turn off Vertical gridlines to remove lines between cells vertically.
• Values:
• Set Text wrap to On for better readability.
• Adjust Row padding and Font size as needed.
• Blank Values:
• Optionally, set the Font color for blanks to match the background to make empty cells less noticeable.
4. Finalize Your Report:
• Your table should now display the Store and Recipe once per group, with the ingredients listed below.
• Highlighted ingredients will be visually distinct based on your conditional formatting.

Example Output:

StoreDisplay	RecipeDisplay	Ingredient
Albany	Chocolate Chunk	Milk (highlighted)
		Chocolate (highlighted)
		Sugar
Annapolis	Superman	Milk (highlighted)
		Chocolate
		Sugar (highlighted)

Notes:

• Why This Works:
• By displaying Store and Recipe only on the first row of each group, you simulate merged cells.
• Removing gridlines and adjusting formatting enhances this effect.
• Conditional formatting on the Ingredient column highlights selected items without affecting the structure.
• Limitations:
• Power BI does not support true merged cells in visuals.
• Empty cells still occupy space, but formatting makes the display cleaner.
• Alternative Approaches:
• Matrix Visual: Since conditional formatting on row headers is limited, the table visual provides a better solution for your requirements.
• Custom Visuals: You might explore custom visuals from AppSource that offer more flexibility, such as the HierarchySlicer or Attribute Slicer, though they may have limitations or require additional configuration.

Conclusion:

Using calculated columns to display group headers only once and applying conditional formatting to the Ingredient column in a table visual allows you to mimic a checklist in Power BI effectively. This method provides a clean and user-friendly display that meets your requirements without the need for complex workarounds or custom visuals.

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

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube