cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dbrandone
Helper III
Helper III

Info needed on schema

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? 

1 ACCEPTED SOLUTION
lbendlin
Super User III
Super User III

"I cannot figure out why Power BI will not allow me to change the relationship to One to Many. "

Check for NULLs on the "one" side.  Those are not permitted in key columns.

 

For the dates columns - you are lucky to have these.  Disable the Power BI auto date/time hierarchies, and learn about the USERELATIONSHIP DAX function.

View solution in original post

1 REPLY 1
lbendlin
Super User III
Super User III

"I cannot figure out why Power BI will not allow me to change the relationship to One to Many. "

Check for NULLs on the "one" side.  Those are not permitted in key columns.

 

For the dates columns - you are lucky to have these.  Disable the Power BI auto date/time hierarchies, and learn about the USERELATIONSHIP DAX function.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors