Filtering twice on the same field

[DataSkills]

Hello, I am attempting to figure out how to get do a count of companies that have a branch but not a head office. 

So my branch table has a field called "Type" with entries "Head Office" and "Branch". I would like to create a measure that counts companies for instances where there is a branch but no head office. (This will be used in a table with regions so we can see how many companies have branches but not head offices per region). 

Companies with branch but no head office = CALCULATE(DISTINCTCOUNT(Branch[CompanyId]), Branch[Type] = "Branch", Branch[Type] <> "Head Office")

I have tried a variety of options so far without success. The above doesn't work because they are mutually exclusive so if a Branch.Type = "Branch", of course it cannot equal "Head Office". But I am struggling to get my DAX to work!

In SQL, it is simply a case of: 

Select count(Distinct CompanyID) from 

Branch where Type = "Branch" and 

CompanyID not in 

(Select CompanyID from 

Branch where Type = "Head Office")

Thanks for any guidance!

[DataSkills]

Hi @some_bih , no that won't work. 

The issue is that we have data where one company can have both a branch and a head office.

Eg in London, Company A has both a head office and a branch. Company B only has a branch. So I want to see a count of 1 for London, because only company B has a branch and no head office. 

[some_bih]

Hi @DataSkills please share your table structure with example what is expected.

[some_bih]

Hi @DataSkills your table is not visible to us so I created some table in Excel and import it in pbi file. I understand that in one single columns are both values Branch and Head office. One possible solution, if your table is organize as shown below could be:

Rename Sheet1 to your table name

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

# Companies with Branch =

CALCULATE(

    DISTINCTCOUNT(Sheet1[CompanyID]),

    Sheet1[BranchType] = "Branch"

)