Help with Syntax - Powerapps Charts Complex requirement

[JustinR]

Hi Guys,

So I have a requirement to display a range of test data, which is currently stored in a SharePoint list, in a line chart in PowerApps. The list holds test data for Chicken feed production and there are numerous types of feed. My problem though is that I need also to add two lines to the chart to show acceptable upper and lower test thresholds. Each feed type has different thresholds. The chart will only display data for one feed type at a time. 

So basically what I am trying to do is make a selection in a dropdown (Feed type) which filters the gallery. I have a button which then collects all of the remaining data in the gallery to feed into the chart. 

I now need to find how to match feed types from the threshold table(also SharePoint) with the feed type from the gallery selection and then display the upper and lower thresholds in the same chart.  I am having real trouble with this. I have tried to add the thresholds as a lookup in the same list as the test data but you can't use lookups in the charts. 

My items code looks like this at present and I was hoping someone could help me discover how to addcolumns to use the thresholds please.

Sort(
    Filter('Feedmill PDI Tests'
        ,
        (Feed_Type.Value = Dropdown1.Selected.Value || If(
            IsBlank(Dropdown1.Selected.Value),
            true,
            false
        )) && (Test_Date >= DatePicker1.SelectedDate && Test_Date <= DatePicker1_1.SelectedDate || If(
            IsBlank(DatePicker1.SelectedDate) || IsBlank(DatePicker1_1.SelectedDate),
            true,
            false
        ))
    ),
    Test_Date,
    SortOrder.Ascending
)

 My items code for the chart is simply the Collections named "PDIChart" 

PDIChart

My threshold data is a single entry for each feed type in a separate SharePoint list. Both lists share the same feed types (Choice) but the thresholds are number columns.

[v-yutliu-msft]

Hi @JustinR ，

Do you mean that you have two lists which share the same feed type and different thresholds, one for upper thresholds, one for lower thresholds?

Do you want to combine these two lists data together and display them as two lines in a line chart?

If so, I think you do not need to use drop down to filter data.

I've made a similar test for your reference:

1)in collection a, I have fields: name,value1

in collection b, I have fields: name,value2

2)To combine these two collections, I use this formula:

ClearCollect(c,AddColumns(a,"value2",LookUp(b,name.Value=a[@name].Value,value2)))

Then collectionc both have value1 field and value2 field. They updates value based on the same name value.

I also find that you can not choose choice type as a line chart's labels, so you also need to add a column with the value of the choice type.

ClearCollect(c,AddColumns(a,"value2",LookUp(b,name.Value=a[@name].Value,value2),"namevalue",name.Value))

On your side, you should try this formula:

ClearCollect(collection,AddColumns(list1,"thresholds2",LookUp(list2,Feed_Type.Value=list2[@Feed_Type].Value,thresholds2),"Feed_Type_Value",Feed_Type.Value))
//thresholds1 field in list1, thresholds2 in list2

 If you want to filter based on date you could try:

ClearCollect(collection,
             Filter(
                    AddColumns(
                            list1,
                            "thresholds2",
                            LookUp(list2,Feed_Type.Value=list2[@Feed_Type].Value,thresholds2),
                            "Feed_Type_Value",
                            Feed_Type.Value
                               ),
                   (Test_Date >= DatePicker1.SelectedDate && 
                    Test_Date <= DatePicker1_1.SelectedDate || 
                    If(
                       IsBlank(DatePicker1.SelectedDate) || 
                       IsBlank(DatePicker1_1.SelectedDate),
                       true,
                       false
                       )
                   )
               )

Then set the line Items to this collection.

Set its number of series to 2, choose labels to Feed_Type_Value, choose series1 to thresholds1,choose series2 to thresholds2.

Then the line chart will display two lines, one is the value of thresholds1, one is the value of thresholds2.

//Do not forget to change the listname, field name with yours.

Best regards,

[JustinR]

Hi,

I only want to select one feed type to display in the chart at any time. The drop down will allow me to filter the test results and the threshold data to display the correct combination of information. 

[v-yutliu-msft · Accepted solution]

Hi @JustinR ,

Do you mean that:

1)in test list, you have these fields: date, test data, feed type?

2)in threshold  list, you have these fields: date, upper thresholds, lower thresholds, feed type?

3)you want to filter these two lists based on feed type and date, display three lines in a chart (one for test data, one for upper thresholds, one for lower thresholds) and the labels is date?

If so, try this:

1)combine two lists together:

ClearCollect(collection1,AddColumns(test list,"upper",0,"lower",0));
ForAll(threshold list,Patch(collection1,LookUp(collection1,Feed_Type.Value=threshold list[@Feed_Type].Value,Test_Date=threshold list[@Test_Date]),{upper:upper thresholds,lower:lower thresholds}))

 2)set the line chart's Items:

Filter(collection1,Feed_Type.Value = Dropdown1.Selected.Value,
                   Test_Date >= DatePicker1.SelectedDate,
                   Test_Date <= DatePicker1_1.SelectedDate)
//drop down 's selected value will not be blank, it has default value
datepicker is the same

Set its number of series to 3, choose labels to Text_Date, choose series1 to upper thresholds,choose series2 to test data,series3 to lower thresholds.

Then the line chart will display three lines, one is the value of upper thresholds, one is the value of test data, another is lower thresholds.The labels are test date. It only display data based one selected feed type and selected dates.

If the answer still not the one that you want, please show me your lists and the chart line that you want in details.

Describing with pics will be better.

Best regards,