topic Re: Apply formatting based on previous column value in DAX Commands and Tips

Apply formatting based on previous column value

tamara_nsb — Mon, 16 Sep 2024 12:30:35 GMT

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.

Re: Apply formatting based on previous column value

lbendlin — Mon, 16 Sep 2024 17:34:45 GMT

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.

Re: Apply formatting based on previous column value

tamara_nsb — Mon, 16 Sep 2024 18:46:25 GMT

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

Re: Apply formatting based on previous column value

lbendlin — Mon, 16 Sep 2024 18:49:33 GMT

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.

Re: Apply formatting based on previous column value

tamara_nsb — Mon, 16 Sep 2024 22:36:58 GMT

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.

Re: Apply formatting based on previous column value

lbendlin — Mon, 16 Sep 2024 22:51:12 GMT

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?

Re: Apply formatting based on previous column value

tamara_nsb — Tue, 17 Sep 2024 17:16:51 GMT

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!

Re: Apply formatting based on previous column value

tamara_nsb — Mon, 23 Sep 2024 13:12:28 GMT

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.