Apologies for another question, but I've been trying for hours to resolve this with no luck...I've done my best to capture what my issue is. In summary I have tried to apply a splicer from one table against data in another table and the results are incorrect. I *think I've created the relationships correctly. And I believe it is as a result of expanding rows within my data, but I can't see to fix it. Here is the detail:
I have three tables from an online SharePoint List within PowerBI. I've managed to transform the data, which involved expanding many of the fields in some of the tables to multiple rows due to there being multiple entries in each record. I have managed to set up the relationships between the tables i.e. 1-many, many-many etc, but I'm having trouble using splicers where the data is from one table and the splicer is from another. My tables are as follows:
Each table has a unique ID and in the original lists, they work using lookups between each one
A further complication is that because I have expanded the rows in the Activity List, one of my fields with numbers (number of attendees) was therefore repeated numerous times and therefore didn't have the correct sum when I was visualising the data. I got around this by counting the repetition of IDs and dividing each number by the number of times it was repeated - following this advice Link to previous question
The problem I now have is that when I visualise data from the Activity list and use a splicer from the organisation list, the number of attendees doesn't present correctly. What it appears to be doing is just taking the first entry for each activity, ever though it might be repeated numerous times - due to the expansion of rows. For instance, if I applied the splicer so it filters to only high street stores (dummy data), I should have the following rows summed to make 14 attendees.
However, when the splicer is applied, it only takes the first value of each activity e.g. 1.666 + 1.5 + 2 = 5.166.
Can anyone help me resolve this?
I have three tables from an online SharePoint List within PowerBI. I've managed to transform the data, which involved expanding many of the fields in some of the tables to multiple rows due to there being multiple entries in each record. I have managed to set up the relationships between the tables i.e. 1-many, many-many etc, but I'm having trouble using splicers where the data is from one table and the splicer is from another. My tables are as follows:
- Organisation list (listing all organisations we work with and info about them - each row is unique with a unique identifier that is not repeated)
- Phonebook list (with individual contacts working at the organisations - I have a lookup field allowing my users to select which organisation an individual works for - which pulls from the previous list) There is a unique identifier in this list, but I have had to expand some rows because of multiple choices in some fields)
- Activity list (every time we email or have contact with someone / and organisation we record the activity here. There are lookup fields to both of the previous lists) There is a unique identifier, but I have had to expand the rows because there are numerous fields with multiple choices which I needed to expand)
Each table has a unique ID and in the original lists, they work using lookups between each one
- Organisation to Activity = one:many (An activity can have many organisations in attendance)
- Organisation to Phonebook = one:many
- Phonebook to Activity = many:many (because there are repeated rows in the phonebook and activity lists - one activity can also have many attendees from the phonebook)
A further complication is that because I have expanded the rows in the Activity List, one of my fields with numbers (number of attendees) was therefore repeated numerous times and therefore didn't have the correct sum when I was visualising the data. I got around this by counting the repetition of IDs and dividing each number by the number of times it was repeated - following this advice Link to previous question
The problem I now have is that when I visualise data from the Activity list and use a splicer from the organisation list, the number of attendees doesn't present correctly. What it appears to be doing is just taking the first entry for each activity, ever though it might be repeated numerous times - due to the expansion of rows. For instance, if I applied the splicer so it filters to only high street stores (dummy data), I should have the following rows summed to make 14 attendees.
| Activity ID | Organisation Name | Attendees (number divided by the number of repeated rows) |
|---|---|---|
| 1 | Pizza Express | 1.666 |
| 1 | KFC | 1.666 |
| 1 | McDonald's | 1.666 |
| 2 | Wilko | 1.5 |
| 2 | Subway | 1.5 |
| 3 | Target | 2 |
| 3 | Starbucks | 2 |
| 3 | Costco | 2 |
| 14 |
Can anyone help me resolve this?
