DAX / filter to count and display organisations with no activity for a certain project

[marchn]

Hello all

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, project 2, project 3 etc).

To provide more context - I have datasets I've imported to PowerBI desktop which I want to analyse, interrogate etc. from three online SharePoint lists:

1. Organisation list (listing all organisations we work with and info about them)
2. 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)
3. Activity list (every time we email or have contact with someone / an organisation we record the activity here. There are lookup fields to both of the previous lists

When I imported the data from a SharePoint list, I had to expant the activity list because a number of the fields have multiple entries. Therefore, ID 1 may appear multiple times. 

Each time I record an activity, there may be multiple organisations involved in a given activity and I also mark which project(s) that activity covered. 

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 (let's say 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

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)

This shows how the rows were expanded in the activity list

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?

[Anonymous · Accepted solution]

Hi @marchn ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) Click "transform data" to enter the power query and select [Organisation] to split. You can check the steps in the steps bar on the right.

(3)We can create a table and measures. 

Slicer = VALUES('Table'[Project or Workstream])

Flag = var _a = SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Project or Workstream]=SELECTEDVALUE('Slicer'[Project or Workstream])),"organisation",[Organisation])
return IF(MAX('Table'[Organisation]) in _a,0,1)

Count = SUMX(SUMMARIZE('Table','Table'[Activity ID],'Table'[Organisation],'Table'[Project or Workstream],"flag",[Flag]),[flag])

(4) Place [Flag]=1 on the visual filter and then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...