Hi Everyone,
I have my data organized but I ran into an issue and I have a general question just for knowledge purposes. First, I recently downloaded report server and a report that I created in Desktop wont open in Report Server because of Many-Many relationships with 2 relationships. One relationship I was able to adjust to get it connected in a one to many fashion, but the other is stumping me. One of the tables in the many to many relationship is a unique column that is connected. It is my UserId column in my User Table. That User table is connected to a referral table, and the UserId field in the referral table would be many since users can enter multiple referrals. I cannot figure out why Power BI will not allow me to change the relationship to One to Many.
Second, The database that I pull data from is quite unique and sort of out of my control. I clean it up the best I can with the queries I run, but the nature of the business we are in leads us to have date/time fields with every process. This means that nearly every table has a date/time field associated with it. When I connect my calendar table to each table, I get many issues with the visuals I create even though the columns in both the calendar table and the table with the date/time field are the same. When I take away the relationship with calendar table and just use the built-in date heirarchy in each table, the data comes out perfect. Outside of diving deeper with the additional columns that a calendar table would bring you, is it still not good practice to use the date/time aspect in visuals from the calendar table versus the date\time columns in the individual tables?
