cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simondpalmer
Helper II
Helper II

Create a color value per Distinct values from a collection

Hi, I have a collection called "ListItems" with a nested collection called "Phase" that I want to give a color per Distinct "Phase" value.

I am collecting a List from a sharepoint site like follows:

 

ClearCollect(ListItems,'Project Schedule')

 

Capture01.JPG

 

Capture02.JPG

 

 

 

 

 

 

 

 

 

I am Getting the Distinct values with the following applied to the gallery item in the top left of the below image:

 

Distinct(ListItems, Phase.Value)

 

But I want to take it a step further by applying a ColorValue to each of these values.

Ideally I want to add it to the collection "ListItems" for each unique ListItems.Phase value. Because I want to apply this to a Gantt Chart for all items that are under that phase. (Gantt Chart is below showing what I want to achieve with colors for each phase):

Gantt-color.jpg

 Any help would be appreciated. This is based on what I have learnt from @Mr-Dang-MSFT 's Gantt Chart idea.

 

-EDIT- I want to cycle through a list of predefined RGB colors shown below. If it gets to the end of the list it just starts from the beginning again.

[{0, 127, 175},{95, 75, 139},{255, 176, 37},{215, 45, 92},{255, 121, 19},{183, 108, 164},{214, 180, 180},{196, 143, 101},

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
simondpalmer
Helper II
Helper II

I figured it out in the end (walking away and coming back with a fresh perspective helps..). After creating the Color table.

ClearCollect(Colors,{ColorIndex:0,Color:RGBA(0,127,175,1)},{ColorIndex:1,Color:RGBA(95,75,139,1)},{ColorIndex:2,Color:RGBA(255,176,37,1)},{ColorIndex:3,Color:RGBA(215,45,92,1)},{ColorIndex:4,Color:RGBA(255,121,19,1)},{ColorIndex:5,Color:RGBA(183,108,164,1)},{ColorIndex:6,Color:RGBA(214,180,180,1)},{ColorIndex:7,Color:RGBA(196,143,101,1)});

 For clarity, I created a Table that combined "Color" by "Phases" by an Index comparison or "LookUp".

ForAll(Distinct(ListItems, Phase.Value), Collect(DistinctPhases, {Value: Result, PhaseIndex: CountRows(DistinctPhases)}));
ClearCollect(IndexedPhases, AddColumns(DistinctPhases,"PhaseColor", LookUp(Colors, PhaseIndex=ColorIndex, Color)));

 Then used "AddColumns" to the embedded Gallery "GalleryTasksGantt" I originally had issues with.

AddColumns(IndexedPhases, "col",LookUp(IndexedPhases, Value=(ThisItem.Phase.Value),PhaseColor))

 

View solution in original post

7 REPLIES 7
ZabiBabar
Continued Contributor
Continued Contributor

@simondpalmer You should rewrite Colors like this and assign a color an index.

 

 

 

 

ClearCollect(Colors,{ColorIndex:0,Color:RGBA(0,127,175,1)},{ColorIndex:1,Color:RGBA(95,75,139,1)},{ColorIndex:2,Color:RGBA(255,176,37,1)},{ColorIndex:3,Color:RGBA(215,45,92,1)},{ColorIndex:4,Color:RGBA(255,121,19,1)},{ColorIndex:5,Color:RGBA(183,108,164,1)},{ColorIndex:6,Color:RGBA(214,180,180,1)},{ColorIndex:7,Color:RGBA(196,143,101,1)}); ClearCollect(Phase, Distinct(ListItems, Phase.Value))

 

 

 

 

And then you can create an indexed list of distinct phases

 

 

ClearCollect(DistinctPhases, Distinct(ListItems, Phase.Value));
ForAll(DistinctPhases, Collect(IndexedPhases, {Value: Result, PhaseIndex: CountRows(IndexedPhases)});

 

 

 

And finally, you can use the formula for the items property of the top left distinct phases gallery:

 

 

AddColumns(IndexedPhases, "Color", Lookup(Colors, ColorIndex=Mod(PhaseIndex, CountRows(Colors))))

 

 

 

Now you are free to use this collection as a lookup for your main gallery.

Lookup(IndexedPhases, Phase=ThisItem.Phase.Value, Color)

 

Best,
Zabi
Mark this post as a solution, or kudo it if you found it be helpful. You can choose more than one answer as solutions, including your own answer. 🙂

Thank you for your reply. So I put all of that together, amongst other irrelevant collections, as follows on home screen "On Visible":

ClearCollect(Colors,{ColorIndex:0,Color:RGBA(0,127,175,1)},{ColorIndex:1,Color:RGBA(95,75,139,1)},{ColorIndex:2,Color:RGBA(255,176,37,1)},{ColorIndex:3,Color:RGBA(215,45,92,1)},{ColorIndex:4,Color:RGBA(255,121,19,1)},{ColorIndex:5,Color:RGBA(183,108,164,1)},{ColorIndex:6,Color:RGBA(214,180,180,1)},{ColorIndex:7,Color:RGBA(196,143,101,1)});
ClearCollect(ProjectFinancials, Blank());
ClearCollect(WIPMonth, PA_WIP_GetMonth.Run());
ClearCollect(ListItems,'Project Schedule');
ClearCollect(DistinctPhases, Distinct(ListItems, Phase.Value));
ForAll(DistinctPhases, Collect(IndexedPhases, {Value: Result, PhaseIndex: CountRows(IndexedPhases)}));

Which works fine. 

Then for the gallery at the top left I use the following:

AddColumns(IndexedPhases, "Color", LookUp(Colors, Color=Mod(ColorIndex, CountRows(Colors))))

 

This comes up with an error message that "Color" is not a relevant color when used as "Fill" for an item inside the gallery:

ThisItem.Color

What have I done wrong? @ZabiBabar 

And how could I use this "Lookup" in my other Gallery which is pulling data from the original ListItems collection. That has the phase under ListItem.Phase.

The collections I have are as follows:

Capture-schedule01.JPGCapture-schedule02.JPGCapture-schedule03.JPG

ListItemsListItems

ZabiBabar
Continued Contributor
Continued Contributor

@simondpalmer  I believe you made a mistake here:

AddColumns(IndexedPhases, "Color", LookUp(Colors, Color=Mod(ColorIndex, CountRows(Colors))))

It should be:

AddColumns(IndexedPhases, Lookup(Colors, ColorIndex=Mod(PhaseIndex, CountRows(Colors))))

 

And then in your other gallery, you can do 

Lookup(IndexedPhases, Phase=ThisItem.Phase.Value, Color)

 

Best,
Zabi
Mark this post as a solution, or kudo it if you found it be helpful. You can choose more than one answer as solutions, including your own answer. 🙂

Thanks for the reply,It still doesn't work as it is expecting 3 arguments and has only received 2.

AddColumns(IndexedPhases, LookUp(Colors, ColorIndex=Mod(PhaseIndex, CountRows(Colors))))

I guess that is why I added "Colors" as a <column name>

ZabiBabar
Continued Contributor
Continued Contributor

Sorry, I made a mistake while correcting yours:

AddColumns(IndexedPhases, "Color", Lookup(Colors, ColorIndex=Mod(PhaseIndex, CountRows(Colors))))

 

The mistake you made was in this part of the formula:

LookUp(Colors, Color=Mod(ColorIndex, CountRows(Colors))

 It should be PhaseIndex instead of ColorIndex. Everything else is correct.

 

Best,
Zabi
Mark this post as a solution, or kudo it if you found it be helpful. You can choose more than multiple answers as solutions, including your own answer. 🙂

But you were right in adding the "Colors" column as a second argument. I am going to edit the original post to highlight that.

Thank you so much that worked for the top left Gallery I just had to add for the "Fill" to be

ThisItem.Color.Color

After the "Items" for the gallery is set to 

AddColumns(IndexedPhases, "Color", LookUp(Colors, ColorIndex=Mod(PhaseIndex, CountRows(Colors))))

----------

However, the main Gallery (GalleryTasks) below circled in red I am still having difficulty as it has an embedded horizontal gallery (GalleryTasksGantt) circled in blue to create the blue bars shown below

 

Capture.JPG

 

 As you can see I have the "Items" set for GalleryTasksGantt as

AddColumns(DateRange,"vis",Date>=(ThisItem.StartDate) && Date<=(ThisItem.DueDate))

(This provides a column called vis to turn on and off the blue bars as they correspond to a DateRange collection.)

The GalleryTasks gallery Items is set to

ListItems

How can I change the blue bar colors to match the Phase value for each of these Tasks?

simondpalmer
Helper II
Helper II

I figured it out in the end (walking away and coming back with a fresh perspective helps..). After creating the Color table.

ClearCollect(Colors,{ColorIndex:0,Color:RGBA(0,127,175,1)},{ColorIndex:1,Color:RGBA(95,75,139,1)},{ColorIndex:2,Color:RGBA(255,176,37,1)},{ColorIndex:3,Color:RGBA(215,45,92,1)},{ColorIndex:4,Color:RGBA(255,121,19,1)},{ColorIndex:5,Color:RGBA(183,108,164,1)},{ColorIndex:6,Color:RGBA(214,180,180,1)},{ColorIndex:7,Color:RGBA(196,143,101,1)});

 For clarity, I created a Table that combined "Color" by "Phases" by an Index comparison or "LookUp".

ForAll(Distinct(ListItems, Phase.Value), Collect(DistinctPhases, {Value: Result, PhaseIndex: CountRows(DistinctPhases)}));
ClearCollect(IndexedPhases, AddColumns(DistinctPhases,"PhaseColor", LookUp(Colors, PhaseIndex=ColorIndex, Color)));

 Then used "AddColumns" to the embedded Gallery "GalleryTasksGantt" I originally had issues with.

AddColumns(IndexedPhases, "col",LookUp(IndexedPhases, Value=(ThisItem.Phase.Value),PhaseColor))

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,185)