topic Re: DAX Query to calculate companies without head office in DAX Commands and Tips

DAX Query to calculate companies without head office

DataSkills — Sat, 29 Jul 2023 09:50:28 GMT

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?

Re: DAX Query to calculate companies without head office

OwenAuger — Sat, 29 Jul 2023 12:13:02 GMT

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

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

Re: DAX Query to calculate companies without head office

ThxAlot — Sat, 29 Jul 2023 18:45:28 GMT

Simple enough

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

Re: DAX Query to calculate companies without head office

tamerj1 — Sat, 29 Jul 2023 19:18:36 GMT

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

Re: DAX Query to calculate companies without head office

DataSkills — Sat, 29 Jul 2023 21:32:37 GMT

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!