Forum Discussion
DAX Summarize
As mentioned before, you have bi-directional filtering between your Date table and your other (fact) tables. This is why you cannot activate the relationship between these tables and your Senders tables.
Furthermore, as I guessed before, your tables all have the same structure and represent segments of your data.
I highly recommend you combine these data from different worksheets and different workbooks into one single table. Make sure you add an additional column for the sender's name.
This link will get you started:
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-combine-binaries/
This will greatly reduce the number of tables in your model, and its complexity overall.
Let us know, if you need further help with that task.
You could do this in the query editor of desktop.
First make a reference copy one of the querys, then append the other tables to this one. You can then remove all columns except Sender. The remove duplicates.
This will give you a new table with all of the unique senders
Definitely do this type of transform in Power Query. Push this as far upstream as possible which will make for a smaller and more efficient Power BI model.
Thanx - will research Power Query
Power Query won't help you with relationships ambiguity but just to obtain a table with the unique values of [Sender] (Which you already have now, right?).
It is a question of data modelling. Please share the model or your relationships view through a screenshot so we better understand the issue.
Thanks
Thanx for the reply. It worked great to get the list I wanted.
However this won't work in a relationship between the different tables.
Thoughts on that or am I missing something obvious.
Received a reply asking if I had a solution to my question - Yes and No.
I am able to get the table with all entries but I can't make it establish all the relationships with all the other tables. Still need that.
Hi GuyJohnson
You can either use Power query as suggested above (referencing queries, deleting the useless columns and remove duplicates in the senders column) or DAX to create a new table.
If you opt for DAX solution, you should go to Modeling > New Table and then:
DsctSenders = Distinct( Table[Sender] )
Your Table can be any table of your model. For performance issue (distinct is an iterator, it iterates over each row of the table) choose the smallest table (e.g with the lowest number of rows but of course make sure it contains all the 'Senders' you need!).
You now have a brand New Table called DsctSenders with a single column containing the unique values of [Sender]. You can link it to the other tables of your model.
