Answered

Best Approach to Using Calendar Table

(1) Share

Report

Posted on by kent-culpepper

I have built a Canvas App with Dataverse back-end.

I am using drop-down filters from my Calendar table, e.g. Year, Quarter, Month, to filter a gallery of items, where the data source\fact table only has the date field.

It is not clear to me how to "connect", or create a "Relationship" with the Date column of my Calendar table and the Date column of my Facts table, which in my mind would be a straight-forward step.

And then further how to use that Relationship to "Filter" the Items in my gallery, based on the drop-down selection(s).

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

KeithAtherton 3,709 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @kent-culpepper,

Creating a relationship may be an option but often the table primary column (string) is used which isn't much help here.

Filtering gallery items is possible using code similar to the following in the gallery items property. Feel free to change the code as required, for example to set data collection and field names:

Filter(colMyData, MyDate = cmbDate.Selected.MyDateColumn )

Filter function reference: https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-filter-lookup

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Follow me online.

Was this reply helpful? Yes No
kent-culpepper on at

Like (0)

Report
Copy link

Link copied!

Hi,

Thanks for your response.

So, there is no way to create a relationship between a Calendar table (Date column) and a Facts table (Date column)? This seems to me one of the more fundamental joins.

Re: your filtering example, yes, I can filter based on date, easy enough. But the objective is to filter based on other dimensions of the Calendar table, e.g. Year, Quarter, Month.

Kent

Was this reply helpful? Yes No
Verified answer

kent-culpepper on at

Like (0)

Report
Copy link

Link copied!

*- Classic "Joins" dont exist @ Dataverse; Generally, Dataverse is architected\designed such that it creates the "Join" upon creation of a new record, where the Table Relationship exists; my question was effectively, how do you retroactively create the "Join", i believe there are multiple ways to do this, by my steps were the following;

1- Create a Relationship @ Fact Table to Calendar Table (named Calendar)
2- Go to each Table and <Edit Data in Excel>
3- Use a VLookup to populate the Fact Table\Calendar Lookup column with the Calendar table\Calendar column (Id)
4- Publish (from Excel Add-In) the Fact Table
5- You can now reference the Calendar table @ Gallery, e.g. Calendar.Year in Dropdown, etc. @ Fx

Was this reply helpful? Yes No