Date from calender table not working in datamodel

[Anonymous]

Hi,

I have created a Business Central datamodel in Power BI and created a calender table. However I cant see Date from calender table combined with data from purchaseDocuments table. Only from G_LEntries, VendorLedgerEntries and VendorInfo.

Can you help? 

Best regards,

Mikkel

[v-gizhi-msft]

Hi,

According to your description, i think table 'purchaseDocuments' doesn't have date field and the one-to-many relationship between 'CS_VendorInfo' and 'purchaseDocuments' depends on another field not date.

So, if you want to combine date from 'Calendar' with data from 'pruchaseDocuments', please try to add the field above said.

Here is my simple sample:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

HI @v-gizhi-msft 

Thanks, 

purchaseDocuments have a date field. Its PostingDate as you can see in my screenshots.

Yes, CS_VendorInfo is just a table with information about Vendors. It doesnt have a date field as it is not relevant in such table. INstead my key between CS_VendorInfo and pruchaseDocuments is VendorNumber. 

Is it impossible to combine calender with purchaseDocuments without the date field in the latter? 

[v-gizhi-msft]

Hi,

Because CS_VendorInfo does not have date field and the relationship between CS_VendorInfo and purchaseDocuments depends on another field called [VendorNumber] as you said.

So if you want to filter purchaseDocuments by date in Calendar, you should add this [VendorNumber] field in table visual instead of directly filtering.

Best Regards,

Giotto Zhi

[Anonymous]

Actually, the datamodel I've created makes sense. The "issue" is that I want a single value on a date (calender) from my many-table purchaseDocuments. The logic for me is not quite clear but I've made a table with date and amount from purchaseDocuments AND using Vendorname from my VendorInfo-table, which works fine. 

[amitchandak]

What all date you have in the purchase table and why are you not able to join. I can see posting date