cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chenko90
Frequent Visitor

Forming relationships with different data

Hi, 

I am trying to create some relationships in my report and am running into issues. I have three different excel sources that i am using in one report. I need to be able to filter them all by month and also by department. They each have both of those columns in their data. When i go to create the relationship it sets it as many to many and then if i try to do another one it gives me an error.....

 

power bi error.JPG

 

I tried doing the create a measure work around but i can't seem to do more than one so then it only lets me filter on one column and i need two. Can someone tell me what i am doing wrong.  

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Forming relationships with different data

Hi @chenko90,

Create. Calendar table and a department table with unique values then use those table to relate all other tables that way you will have 1 to many relationships and will be able to make all the calculations on your model.

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


View solution in original post

3 REPLIES 3
Super User II
Super User II

Re: Forming relationships with different data

If Power BI is forcing it to many-to-many then that means you don't aren't connecting two fields that are unique in one of the tables. Can you post a screenshot of your model, and a bit more details on what you mean when you say you filter on two columns and it won't let you? Filtering on 2+ columns shouldn't be an issue.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Super User III
Super User III

Re: Forming relationships with different data

Hi @chenko90,

Create. Calendar table and a department table with unique values then use those table to relate all other tables that way you will have 1 to many relationships and will be able to make all the calculations on your model.

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


View solution in original post

Highlighted
Super User IV
Super User IV

Re: Forming relationships with different data

Many to many is supported but it should not create two patch for the same set of table.

In case you have dates. It is always preferred to use date calendar.

You can also create a month calendar

 

MonthTable = 
    var FullCalendar = ADDCOLUMNS(CALENDAR("2016/1/1","2017/12/31"),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMMdd"),6),"Month Name",FORMAT(MONTH([Date]),"MMM"),"Year-MonthName",YEAR([Date]) & " " & FORMAT(MONTH([Date]),"MMM"))
    return 
    SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName])

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors