Displaying data with 1 to many relationship in a dashboard or report

[CYParker]

Hi All,

I'm a new Power BI user with a problem I've been trying to solve for a couple of weeks to no avail so am coming here for assistance!

Here are some dot points of the current situation:

• I'm trying to create a report based on data from our Dyn 365 CRM
• Our key entity (Entity 1) has many different entities (Entity 2, Entity 3, Entity 4 etc) associated with it, with each 'Entity 1' generally having many Entity 2's or 3's etc. (Many to 1 relationship)
• I've combined 5 datasets into 1 (Entities 1, 2, 3, 4 and 5) using the append functionality
  • Each entity has a id field which is relatable to an id field in entity 1
  • there are only 1 or two fields from each entity which I would like to display in a visual table
• I'm trying to display this data in a neat table (in a report) but can't figure a way to do this
• Using the table visual, it displays like this: 
  • but I would like something like this:
  • 
• I've tried using the Matrix visual but it proves a bit difficult due to the seemingly clumsy way the horizontal scrolling works
• Also tried manipulating the data table playing around the pivot settings (as described here) but it didnt seem to work the way I would have liked it
  
  • maybe because I'm trying to work with many different entities that link to entity 1
• My next thought was to combine multiple cells into one (E.g. find all field X's from entity 2 that relate to entity 1 and merge into one cell; similiary with other entities) but I can't work out how to do this

Can anyone offer any suggestions?

[PBI_User_248]

This is exactly the problem I am trying to solve now. Were you able to find a solution?

[v-diye-msft]

Hi @CYParker ,

If you'd like to group the table based on the category in entity 1, please right click the column and try "new group“.

If you'd like to combine 2 columns into one, please try the DAX : CONCATENATE([code1],[code2])

Best regards,

Dina Ye

[CYParker]

Hi @v-diye-msft 

Regarding Grouping

I can see the benifit of this in some scenarios, but in this case it doesn't acheive the same affect as what I was looking for (unless I'm not using it properly). 

I've tried using a Matrix visual which does display the data how I would like, but the restrictive horizonal scrolling was a problem.

Regarding Concantenate

I'd really like to merge rows as opposed to columns.

For example, John Smith (in entity 1) has 3 different Entity 2 records associated. I'd like to combine that data (relating to John Smith) from entity 2 into one cell (as opposed to three).... I have a feeling that there is a DAX function to do this but am not sure how to do it 😕

[v-diye-msft]

Hi @CYParker ,

Could you please share me your dummy pbix? (make sure the confidential info been removed before), it would be helpful for us to perform the reults.

Best regards,

Dina Ye

[CYParker]

Hi  ,

Thanks for responding to my query.

Hopefully you can access the dummy pbix here

[CYParker]

Also worth noting is that while it doesnt look so bad now with there only being one extra entity (Entity 1 + Entity 2) there will be another 3 entities worth of data I'd like displayed in the visual alongside this data, so as it is, the data can look very messy