DAX Summarize

[GuyJohnson]

Need some help. I'm new to Power BI and DAX and I'm having some trouble.

I have numerous tables in my Power BI desktop. Each of these tables have a column named Sender. Since I can't create a direct relationship between them I wanted to  create a new table that listed each of the senders. That way I can establish a relationship between everything.

What would be the format of the DAX statement to do this??

[paulag]

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

[Phil_Seamark]

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.  

[GuyJohnson]

Thanx - will research Power Query

[Datatouille]

@GuyJohnson

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

[GuyJohnson]

To everyone who has been assisting with this issue I’m having thanx. All replies have helped and I have learned something from all of them.

Let me restate the problem and attach a screen shot of my relationships. This way we can all get on the same page.
I have an Excel Spreadsheet that has numerous tabs on it that track checks we receive for various areas within the company. Each sheet in the Spreadsheet is named by year. I brought the Spreadsheet into Power BI desktop along with a separate Date table. The Date table is a standard one I’ve used successfully many times to allow me to slice my data by data.

My issue started when I tried to take the column named Company Sending Check from each Sheet on the Spreadsheet. The sheets are named Alpont 2010, Alpont 2011, etc. That column does not contain all the Companies so I needed a table to list all names so I could slice by company.

@paulagwas very helpful to show me how to get this table and it worked – but it won’t establish the relationships I need. This is table Alpont Reference

@Datatouillesuggested another way to get my table and it also was very useful – but again it won’t establish my relationships. This is table Alpont Senders

Currently I have a relationship from the Date table to each of the other tables between the dates and it works for slicing on dates.
When I try to establish relationships, I get what is in the screen shot.

He arrows going off the page(screenshot) are to other sheets on the Spreadsheet that contain similar but different data that I haven’t started to work with. The relationships there are Date only.

Where am I going wrong???

[LaurentCouartou · Accepted solution]

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.

[GuyJohnson]

I got things working once I recreated the relationships correctly.

I'm unable to combine the Data as the department wants it kept in seperate shets in the Spreadsheet - I can work on that.

Everyone has been very helpful and I've learned alot.

[LaurentCouartou]

Just to make sure there is no doubt about what I meant: you can combine the data in one single table in the model, without modifying the underlying source.

I recommend you take a look into this.

[GuyJohnson]

Thanx for your patience with me as I learn this all.

Will look into your suggestion

Once again thanx to all who contributed.

[GuyJohnson]

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.

[GuyJohnson]

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.

[Datatouille]

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.

[GuyJohnson]

Does not work for creating the relationships. I get this:

You can't create a direct relationship between Senders (My Table name) and Senders 2015 (Table with some of the names) because that would introduce ambiguity between tables Senders 2015 and Senders 2016. To make this relationship active, deactivate or delete one of the relationships between Senders 2015 and Senders 2016.

I don't have any relationships between those tables that are showing up. They are only in the 2 tables I created via the above posts.

I'm confused????

[LaurentCouartou]

This error message is due to using bi-directional filtering in your model.

This comes with a few restrictions:

https://msdn.microsoft.com/en-us/library/mt591991.aspx

In your case, it may make sense to only use one-way filtering.

Also, you said you had two tables named 'Senders 2015', and 'Senders 2016'. Do these tables share the same structure? In that case, you may want to use "Append Queries" in the query editor, so you only have one table containing all your data for 2015 and 2016.

This would be another way to solve your issue.

[Datatouille]

@GuyJohnson

That's strange. You must have other relationships introducing ambiguity in the model.

Can you share the model or send us a screenshot of your relationship view ?

[GuyJohnson]

The only other relatonship in the model is a Date field coming from a seperate Date table