Dear Concern,
I would like to have Data Model corrected, I am trying to connect the Date Table with the other Table which has Dates and I am unable to create the "One to many Relation when i try to change the cardinality it give me the error
Hi @mohammedkhan11 , we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
- Use the forum search to discover if your query has been asked before by another member.
- When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
- Use the proper category that best describes your topic
- Provide as much context to a question as possible.
- Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.
Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.
Hi there,
You should take it into your consideration that the both tables have to to have date columns in date format.
One of the tables must act as dimension, the other as fact table.
At the same time, I’m sharing following link for your further consideration.
Good luck…
Hi @mohammedkhan11, we’ve noticed that no response has been received from you since the 11th of March. Did @IlgarZarbaliyev help solve your query?
We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Comprehensive Report.pbix (6.6 MB)
Yes, I need help in the attached report, I have attached the .PBIX file hopefully he would understand by looking at the file.
Hi @mohammedkhan11,
You didn’t share which tables failed to connect and more importantly which keys you tried to use but I do have a few obervations I’d like to share.
- You didn’t mark your Date table as Date table
- Looking at your report and your date table, the lowest granularity in your model is the daily level. The date table date is your key and you should only create relationships to other tables at that daily level (not on months, years or whatever - in those cases use TREATAS to do it virtually).
- Most of your fact tables have a datetime type field which means the granularity of those columns is at the level of seconds and not of days… This massively increases the cardinality and more importantly means the grains don’t match between your date- and fact tables.
- If you are not going to report on any level of time, remove the time portion from all date fields in your model. This can be done in Power Query by selecting your datetime columns and on the Transform tab go to Date/Date Only.
- However should you want to report on both date and time, split all datetime fields and include a separate time dimension table in your model, you can find an example of a time table in the M code showcase. This will decrease cardinality, reduce model size and increase performance.
I hope this is helpful.
Hi @mohammedkhan11, did the response provided by @Melissa and @IlgarZarbaliyev help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @mohammedkhan11, we’ve noticed that no response has been received from you since the 15th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @mohammedkhan11, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
I would like to have this result bhai, I have a Table called disbursement. In the disbursement there is agreement.
Each Agreement has an AgreementGrant Amount.
In the same table there is a processed Dates on which the Agreement amount was paid,
The Amount column is the amount.
Processed Dates are the dates where this amount was processed.
Agreement amount (-) Amount that is processed = Balance
Now I want Each something like shown below.
First 5 Column shows the 2021 Snipping Sheet, where in the total amount is the "TOTAL Amount which has been processed in the previous dates / year) but the next Column of 2022, 2023 so on
it should only show the total balance for that year.
This is the futuristic Model that I’d like to show by tomorrow and i have no clue what to do.
- You can see:
First Section of 2021 has 5 columns; however the rest only shows the balance of that year.
and the link to the file is already given.
Please provide the solution for the same, apologies for not returning on your messages, inconvenience is highly regretted.
Book1.xlsx (646.5 KB)
DB File also Uploaded