Forum Discussion
Conditional Format all columns up to a certain column based on values
I'd like to conditional format the background color of a cell based on whether or not a column further down has valid data.
The data given is as follows:
| Team | Phase | Subphase | Value |
| Team 1 | 1 | A | 1 |
| Team 1 | 1 | B | 1 |
| Team 1 | 1 | C | 1 |
| Team 1 | 2 | A | 1 |
| Team 2 | 1 | A | 0 |
| Team 2 | 1 | B | 0 |
| Team 2 | 1 | C | 0 |
| Team 2 | 2 | A | 1 |
| Team 3 | 1 | A | 1 |
| Team 3 | 1 | B | 1 |
| Team 3 | 1 | C | 0 |
| Team 3 | 2 | A | 1 |
| Team 3 | 3 | A | 1 |
This is what I would like the result to be. For Team 1, all of Phase 1 (A, B, and C) has valid data. They are ok to start on Phase 2A.
Team 2 skipped Phase 1 entirely (1A, 1B, 1C have 0 entered in the cell) and started on Phase 2A. This isn't allowed, so the Phase 1 cells with 0s in them should be red.
Team 3 did 1A and 1B, but skipped 1C and started on Phase 2A and 3A. It is ok for them to start on 3A without completing all of Phase 2, but it is not ok for them to start on 2A without completing all of Phase 1. Therefore, only 1C is red.
3 Replies
Anonymous , Try using below method
Create a New Column for Validation
Open your Power BI Desktop and load your data.
Go to the "Data" view.
Create a new column by clicking on "New Column" in the Modeling tab.
Use the following DAX formula to create a validation column:
Validation =
VAR CurrentTeam = 'Table'[Team]
VAR CurrentPhase = 'Table'[Phase]
VAR CurrentSubphase = 'Table'[Subphase]
VAR CurrentValue = 'Table'[Value]
VAR PreviousPhases =
FILTER(
'Table',
'Table'[Team] = CurrentTeam &&Apply Conditional Formatting
Go to the "Report" view.
Select the table visual where you want to apply the conditional formatting.
Click on the down arrow next to the column you want to format (e.g., Value).
Select "Conditional formatting" > "Background color".
In the "Background color" window, select "Field value" under "Format by".
Choose the Validation column created
Set the color for "Invalid" to red and "Valid" to no color or a different color as needed.Hi,bhanu_gautam .
Hello,Anonymous .I am glad to help you.
Like this?I noticed that in the original data you provided you had already processed the data for the conditions you described: they were correctly labeled as 0, so I marked the cells that were not empty and had a value equal to 0.
Is your original data also the same as the test data you provided: the data is marked as 0 beforehand, if so, you only need to mark the cells that have a value of 0.I created three dimension tables based on the fields
Phase_ = VALUES('Table'[Phase]) Subphase = VALUES('Table'[Subphase]) Team_ = VALUES('Table'[Team])After placing them in the matrix, it relies on relational filtering to show null values (data that doesn't exist by itself)
Relying on the filtering of the matrix itself, filtering out cells with value = 0, as you mentioned in your description, indicates a step that should not be skipped (which is actually skipped)
If your actual data and your test data is not consistent with the definition of the criteria, please tell later and promptly correct my misunderstanding.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
