Forum Discussion

GuyJohnson's avatar
GuyJohnson
Kudo Collector
9 years ago
Solved

DAX Summarize

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

Need Help
  • LaurentCouartou's avatar
    LaurentCouartou
    9 years ago

    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.

16 Replies

  • paulag's avatar
    paulag
    Advocate I
    9 years ago

    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's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      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's avatar
      GuyJohnson
      Kudo Collector
      9 years ago

      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's avatar
        GuyJohnson
        Kudo Collector
        9 years ago

        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.