Hello all
I have a PowerBi dashboard which captures activities with organisations we work with. I have an Org lists, phonebook list and an activity list, all linked with relationships via unique IDs. In the activity list I could record that we sent emails, or ran webinars etc and list which organisations received / attended against that record. Each activity is recorded against a project and sometimes multiple projects. (e.g. project 1, project 2, project 3 etc)
I am trying to write some DAX (or something?) to help me count and display the number of organisations which do not have any activity recorded for a certain project e.g. project 1.
When I imported the data from a SharePoint list, I had to expand the activity list because a number of the fields have multiple entries e.g. multiple organisations attended a single activity which could be against multiple projects and multiple attendees etc.
The issue I'm having is that I can't use filters to show me all the organisations that don't have any activity recorded for a given project 1. Because that organisation may have activity recorded for project 1 on another row, so when I filter out all the lines for project 1, the organisation name still remains in there, because there was another entry which had project 2 / project 3 recorded against it. I've done screen shots below to illustrate with sample data.
Image 1 - Raw data -As you can see the only organisations which don't have any entries for project 1 are Sainsbury's, Costco and McDonalds (so a count of 3)
Image 2 - This shows how the rows were expanded in the activity list
Image 3 - This shows the impact of when I use filters or DAX with the filter function - it returns WH Smith and Pizza Hut because I've filtered out the entries for project 1, but of course they did have entries for project 1.
Can anyone help me work out how to count the number and display the names of the organisations that don't have any entries for project 1?
I have a PowerBi dashboard which captures activities with organisations we work with. I have an Org lists, phonebook list and an activity list, all linked with relationships via unique IDs. In the activity list I could record that we sent emails, or ran webinars etc and list which organisations received / attended against that record. Each activity is recorded against a project and sometimes multiple projects. (e.g. project 1, project 2, project 3 etc)
I am trying to write some DAX (or something?) to help me count and display the number of organisations which do not have any activity recorded for a certain project e.g. project 1.
When I imported the data from a SharePoint list, I had to expand the activity list because a number of the fields have multiple entries e.g. multiple organisations attended a single activity which could be against multiple projects and multiple attendees etc.
The issue I'm having is that I can't use filters to show me all the organisations that don't have any activity recorded for a given project 1. Because that organisation may have activity recorded for project 1 on another row, so when I filter out all the lines for project 1, the organisation name still remains in there, because there was another entry which had project 2 / project 3 recorded against it. I've done screen shots below to illustrate with sample data.
Image 1 - Raw data -As you can see the only organisations which don't have any entries for project 1 are Sainsbury's, Costco and McDonalds (so a count of 3)
Image 2 - This shows how the rows were expanded in the activity list
Image 3 - This shows the impact of when I use filters or DAX with the filter function - it returns WH Smith and Pizza Hut because I've filtered out the entries for project 1, but of course they did have entries for project 1.
Can anyone help me work out how to count the number and display the names of the organisations that don't have any entries for project 1?
