Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
tamara_nsb
Helper I
Helper I

Apply formatting based on previous column value

 
 

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.

 

ItemTESTScorePhase
Item1A21
Item1A12
Item1A33
Item1B21
Item1B12
Item1B33
Item1C21
Item1C12
Item1C33
Item2A12
Item2A33
Item2B12
Item2B33
Item2C12
Item2C33

 

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

Phase111222333
TESTABCABCABC
Item1222111333
Item2REDREDRED11133

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.

1 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?

 

lbendlin_0-1726527053848.png

 

View solution in original post

7 REPLIES 7
tamara_nsb
Helper I
Helper I

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
Helper I
Helper I

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
Super User
Super User

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.

lbendlin_0-1726507941077.pnglbendlin_1-1726507956847.pnglbendlin_2-1726507966614.png

 

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

 

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.

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
ItemTESTScorePhase
Item1A21
Item1A82
Item1A310
Item1B51
Item1B82
Item1B93
Item1C71
Item1C82
Item1C103
Item2A61
Item2B61
Item2C61
Item2A93
Item3A93
Item3B93

 

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

tamara_nsb_0-1726526044671.png

 

 

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?

 

lbendlin_0-1726527053848.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors