Forum Discussion

DataSkills's avatar
DataSkills
Icon for Resolver I rankResolver I
3 years ago

Filtering twice on the same field

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!
"Need Help"

3 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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"
    )

     

  • DataSkills's avatar
    DataSkills
    Icon for Resolver I rankResolver I
    3 years ago

    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.