FIlter Charts

(0) Share

Report

Posted on by JotaJr_1

Hello Peoples, good morning.

I would like a help to create a filter between dates( with two datapickers) , department (SP Column)

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Mr-Dang-MSFT Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!
Hi,

I retyped your formula here:

AddColumns( GroupBy( Lista_BP_System; "Planta"; "ByRatingPlanta" ); "CountOFRatingsPlanta"; CountRows(ByRatingPlanta) )

If you wanted to filter the table, it needs to be done before the columns are grouped. Here's an example:

AddColumns( GroupBy( Filter( Lista_BP_System; DateStart>=DatePicker1.SelectedDate; DateEnd<=DateAdd(DatePicker2.SelectedDate,1,Days) ); "Planta"; "ByRatingPlanta" ); "CountOFRatingsPlanta"; CountRows(ByRatingPlanta) )

This means, "Filter Lista_BP_System to show only records whose start and end dates are between the dates picked in the date pickers. Then group the resulting records by Planta. Add a column that shows a count of the grouped records."

Note that the second date picker needs to add a day to be inclusive of the selected date.

Let me know if this helps.

Was this reply helpful? Yes No
JotaJr_1 10 on at

Like (0)

Report
Copy link

Link copied!

heeey sorry, I'm new and I'm interested in learning Is this DataStart and Data End a Column from my list on the sharepoint?

Obs: As I have a different language in my powerapp .. some things are different. The "." is ";" and ";" is ";;"

AddColumns(
GroupBy(
Filter(
Lista_BP_System;
'Data Ref' >=DatePicker4.SelectedDate;
'Data Ref' <= DateAdd((DatePicker3.SelectedDate;1;Days)
);
"Planta";
"ByRatingPlanta"
);
"CountOFRatingsPlanta";
CountRows(ByRatingPlanta)
)

Was this reply helpful? Yes No
Mr-Dang-MSFT Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @JotaJr_1 ,

When you hover your mouse over the red error on 'Data Ref', what is the error message? Can you type it here? Is that column in the table?

Was this reply helpful? Yes No
JotaJr_1 10 on at

Like (0)

Report
Copy link

Link copied!

"Invalid argument type. Expected a Number value"

Data ref is my reference column

with plain text

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @JotaJr_1 ,

Is the "Data Ref" a Text type column in your SP List? Why do you not add a Date Type column to store the date value in your SP List?

Regarding the screenshot that you mentioned, it seems that you add a Text type column in your SP List to store the date value, and it only store month value and year value. But the selected date value using the Date Picker control contains Month, Day, and Year value.

Within your Filter formula, you could not compare a Text value with a Date value directly. As an direct solution, please consider convert the "Data Ref" from Text type to Date type (Date Only) column in your SP List. Then modify your formula as below:

AddColumns( GruopBy( Filter( Lista_BP_System; DateValue(Text('Data Ref'; DateTimeFormat.ShortDate)) >= DatePicker1.SelectedDate; DateValue(Text('Data Ref'; DateTimeFormat.ShortDate)) <= DatePicker2.SelectedDate; If( // filter reocrds based on seleted Department option IsBlank(DepartmentComboBox.Selected.Value); true; Department = DepartmentComboBox.Selected.Value ) ); "Planta"; "ByRatingPlanta" ); "CountOfRatingsPlanta"; CountRows(ByRatingPlanta) )

Note: The DepartmentComboBox represents a ComboBox control added in your canvas app, to list available Department options, which you want to use filter your data source records.

Please try above solution, check if it could help in your scenario.

Regards,

Was this reply helpful? Yes No