cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Transporter007
Level: Powered On

Visibility on Calendar

Hi, 

 

I am trying to modify the calendar template, especially the circle that are there to show if there's any data on the calendar.
The calendar works right of the box, but the have used CountRows and because I have a few entries on almost all dates, the speed of the app is reduced a lot.

I have been trying to solve it by an If-statement but I cannot get to work.
Here is the code I am currently using, which works but the performance is horrible.

CountIf(behandlingar;DateValue(Text(Datum_behandling)) = DateAdd(_firstDayInView;ThisItem.Value;Days))


Thank you all for your help, it's highly appreciated.

1 REPLY 1
Power Apps Staff Mr-Dang-MSFT
Power Apps Staff

Re: Visibility on Calendar

Hi @Transporter007 , 

It sounds like you want to:

  • show a count of events on a calendar

But to do this, you have to run a count on each row of the calendar. I have a few suggestions here--big ideas below:

  1. Evaluate the date against the table so you do not need to calculate it in your filters.
  2. Collect treatments for the dates shown in the calendar only. Then show counts against the collection instead of the underlying behandlingar table.

 

The calendar template uses a table of 42 integers, each representing a day on a calendar, 0-41. This results in 6 rows of 7 days each when shown in the gallery. A date is calculated in the items of the gallery:

 

DateAdd(_firstDayInView;ThisItem.Value;Days)

 

 

In your CountIf, you are performing that calculation again and again for each row of the table. It's inefficient to do the calculation and filter at the same time. So let's complete that calculation ahead of time. We'll append it as a column to the underlying table itself, rather than figuring it out wherever it is needed:

 

AddColumns(
    [0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19; 20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41];
    "Date";DateAdd(_firstDayInView;Value;Days)
)

 

This means, "Add a column called Date that is equal to each respective integer plus the first date in view." So the 0th date would be the first date in view. The table changes from one column (Value) to two columns: Value, Date. Now you can reference Date anywhere in the gallery.

 

Now that the Date can be referenced, there's a few things we can do. If you were to put your existing filter into the gallery, it would make a call to the connected datasource 42 times. That's a lot of calls. Let's do one call instead--get all of the treatments for the date range that is shown. You can place this action wherever you cause the calendar's date range to change.

 

ClearCollect(colTreatments;
    Filter(behandlingar;
        DateValue(Text(Datum_behandling)) >= First(Gallery1.AllItems).Date;
        DateValue(Text(Datum_behandling)) <= Last(Gallery1.AllItems).Date
    )
)

 

This means, "Collect all of the treatments between the first and last date in the gallery named Gallery1. Put them into the colTreatments collection (name it whatever you want)."

 

Now inside the gallery, you can do the CountIf or CountRows against the collection instead of behandlingar.

 

One final suggestion: I notice that you are using DateValue() and Text() around Datum_behandling. That's causing the filter to be more complicated. Can you modify the table to make the column a date type? This way you do not need to do any conversions.

 

Let me know if you need any clarification.

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (4,223)