Apply formatting based on previous column value

[tamara_nsb]

I need to change background on matrix fields if the "next" phase has a value.   I have data as shown,  each item has a score for 3 tests.  The tests are done 3 times,  once for each phase.   If any item completes tests in Phase 2 or 3 with no score in Phase 1 then I need to color phase 1 red as missing.

Item	TEST	Score	Phase
Item1	A	2	1
Item1	A	1	2
Item1	A	3	3
Item1	B	2	1
Item1	B	1	2
Item1	B	3	3
Item1	C	2	1
Item1	C	1	2
Item1	C	3	3
Item2	A	1	2
Item2	A	3	3
Item2	B	1	2
Item2	B	3	3
Item2	C	1	2
Item2	C	3	3

Expected Output - Where it says RED it should be blank with a red background.  Phase and Test are column headers 

Phase	1	1	1	2	2	2	3	3	3
TEST	A	B	C	A	B	C	A	B	C
Item1	2	2	2	1	1	1	3	3	3
Item2	RED	RED	RED	1	1	1	3	3	3

So I need a way to know,  Am I in phase 1A and does phase 2A have a score.    Not sure how I write this?

Any help or just pointing me where to start would be great.

[tamara_nsb]

Thanks Again for pointing me in the right direction between TreatAs and SelectedValue commands I was able to get everything working as desired!   Don't know what I would do without this community.   

[tamara_nsb]

Thanks again for the explanation.     They want to fill left with red once there is any score in a higher phase.   So I do have to add that to the logic, thanks for pointing it out. 

I believe I understand now how to apply the logic.   the SELECTEDVALUE function was what I was missing.   I think I was way overcomplicting trying to add and remove filters.

Thanks

Thanks for your help! 

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins. Here are the basic mechanics. You can add your specific logic.

[tamara_nsb]

Sorry I am still a confused.    I have duplicated your tables and looked into the treatas function but I don't understand how it would be applied here.

Just to make sure we are on the same page.   If I updated the input data to add one more row for item 3 with only a score for Phase 3, TEST A   I would expect the Item 3 row to be blank except for 1A and 2A being red.   

If it helps the logic is,  you shouldn't do Phase 2  unless 1 is complete, dont do 3, until 1 & 2 are complete.  So once they enter data for Phase 2 or 3,  the previous phases should be red to show they are incomplete.

To get the background for Phase2 Test A I need to know the score for Phase3 TestA  

I have tried adding a "NextPhase"  to the TestPhases table and tried something like this.   But obviously doesn't work.

bg2 = CALCULATE(SUM('Table'[Score]), FILTER('Table', 'Table'[Phase] == 'TestPhases[NextPhase])))

Thanks

[lbendlin]

I gave you the general approach of reporting on things that are not there. You can add on to that by implementing your logic.

The sample data you provided did not cover all possible scenarios.  See if you can provide better sample data.

[tamara_nsb]

Hi lbendlin

So I have been trying to understand what you sent me and so far got nowhere.   Sorry I keep needing more help.

When you say "Add on to that by implementing your own logic"  Where would I implement it?

Score_ = CALCULATE(max('Table'[Score]),TREATAS(Items,'Table'[Item]),TREATAS(SUMMARIZE(TestPhases,[Phase],[TEST]),'Table'[Phase],'Table'[TEST])) & ""  

Do you mean where the "& ""  is in the score_ measure?  

I did try something like this,  

CALCULATE(max('Table'[Score]),TREATAS(Items,'Table'[Item]),TREATAS(SUMMARIZE(TestPhases,[Phase],[TEST]),'Table'[Phase],'Table'[TEST]), Filter(ALLEXCEPT('TestPhases', 'TestPhases'[Phase])), 'TestPhases'[Phase] == "2" && 'TestPhases'[TEST] == "A")

I tried adding a "Next Phase" to the TestPhases table so maybe I could do something with that.

I think I need to create a new score that  removes the phase filter  ( All, Allexcept ?) ,  Then apply a new filter to the phase to calculate _scoreNextPhase

Thanks Again

Below is new data and expected results

Item	TEST	Score	Phase
Item1	A	2	1
Item1	A	8	2
Item1	A	3	10
Item1	B	5	1
Item1	B	8	2
Item1	B	9	3
Item1	C	7	1
Item1	C	8	2
Item1	C	10	3
Item2	A	6	1
Item2	B	6	1
Item2	C	6	1
Item2	A	9	3
Item3	A	9	3
Item3	B	9	3

Nothing in phase 3 will be red because there is no phase 4.   

[lbendlin · Accepted solution]

Please validate your test data.  I think a 3 and a 10 are switched (item1, A), and a 9 is missing from the expected output (item2, 3A)

Why would item3, 1A and 1B be red when the 2A and 2B cells are empty?