DAX Query to calculate companies without head office

[DataSkills]

Hi, 

I am attempting to calculate how many companies have no head office. 

COMPANY table

CompanyID	CompanyName
1	Acme
2	Enterprise
3	Joes business
4	PowerCleaners

BRANCH table

CompanyID	BranchType	BranchCity
1	Branch	London
1	Head Office	Leeds
1	Branch	Manchester
2	Branch	Hull
3	Branch	Manchester
3	Branch	London
4	Head Office	Bristol
4	Branch	Bristol
4	Branch	Leeds

The answer I am expecting for the above scenario would be 2. Enterprise only has a branch and Joes business only has branches. The other 2 companies both have head offices. 

I've been using DAX Studio and so far, what I have is:
DEFINE
    VAR HO =
        SELECTCOLUMNS (
            CALCULATETABLE ( Branch, Branch[BranchType] = "Head Office" ),
            "CompanyID", Branch[CompanyId],
            "HOValue", Branch[BranchType]
        )
    VAR BRA =
        SELECTCOLUMNS (
            CALCULATETABLE ( Branch, Branch[BranchType] = "Branch" ),
            "CompanyID", Branch[CompanyId],
            "BranchValue", Branch[BranchType]
        )
   
    VAR COMBO =
        SELECTCOLUMNS(DISTINCT ( FILTER ( NATURALLEFTOUTERJOIN ( BRA, HO ), ISBLANK ( [HOValue] ) ) ), [HOValue])


EVALUATE
COMBO

This gives me a perfect 2 line table with the blank HOValues (which is just want I expect). However, when I attempt to do any kind of countrows or count or anything like that to try to get the number 2 back, it fails. I am stuck! Any suggestions? 

[tamerj1 · Accepted solution]

HI @DataSkills 
If you're looking for a measure, please try

Branches Only Companies =
COUNTROWS (
    FILTER (
        VALUES ( Branch[CompanyId] ),
        ISEMPTY (
            FILTER ( CALCULATETABLE ( Branch ), Branch[BranchType] = "Head Office" )
        )
    )
)

[ThxAlot]

Simple enough

Company Without Headoffice = 
FILTER( COMP, CALCULATE( ISEMPTY( BR ), BR[BranchType] = "Head Office" ) )

[OwenAuger]

Hi @DataSkills 

DAX Studio can only execute DAX queries, which must return a table.

So if you change the end of the query to

EVALUATE COUNTROWS ( COMBO )

you'll receive an error because COUNTROWS ( COMBO ) is a scalar expression.

Instead, you can return a 1x1 table containing the scalar value, e.g.

EVALUATE
{ COUNTROWS ( COMBO ) }

or

EVALUATE
ROW ( "Company Count", COUNTROWS ( COMBO ) )

On the other hand, if you want to create a measure returning the same value, the measure must return a scalar value and you could reuse the above code (adjusted slightly) with the final step returning COUNTROWS ( COMBO ).

Regards

[DataSkills]

@OwenAuger thank you for this response. That makes sense now. You have taught me an important distinction between Dax Studio and Power BI's measure interface!