Forming relationships with different data

[chenko90]

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.....

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.  

[amitchandak]

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

[MFelix · Accepted solution]

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.

[edhans]

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.