Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to 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?
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.
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!
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.
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?
I did try something like this,
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
21 | |
18 | |
14 | |
11 |
User | Count |
---|---|
43 | |
35 | |
25 | |
22 | |
22 |