Create a color value per Distinct values from a collection

[simondpalmer]

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')

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):

 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},

[Anonymous]

@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. 🙂

[simondpalmer]

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? @Anonymous 

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:

ListItems

[Anonymous]

@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. 🙂

[simondpalmer]

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>

[Anonymous]

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.

[simondpalmer]

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

 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 · Accepted solution]

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))