DAX - counting rows of distinct parent in child table

Question

I might be bad at googling as I can't find an answer to this seemingly simple puzzle.

I have the following table in a tabular cube (2013)

**ProjectId Department  Region  Amount**
    100        Dep1      Reg1    300
    102        Dep1      Reg1   -300
    103        Dep1      Reg1   -100
    104        Dep2      Reg1   300
    154        Dep2      Reg1   400
    187        Dep2      Reg1   -200
    198        Dep3      Reg1   -100
    199        Dep3      Reg1   300

The first three attributes are part of an organisational hierarchy. I want to create a measure that counts the number of departments with an aggregated [Amount] less than 0. In this case, 'Dep1' is -100 and should be counted as '1' when looking at the parent region (Reg1).

Any ideas on how this measure can be written - so that it can be analysed using the organisational hierarchy in this table? All help is much appreciated!

I have tried the following:

Creating a separate table listing only the departments, adding a calculated column aggregating the amounts from each child, and checking if the result < 0.
Create a measure based off #1: 'Calculate(countrows('Department');IsDepartmentNegative = 1).

This measure only yielded the total number of departments - but could not be analysed based on the organisational hierarchy from the main table used in the example.

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-02-01T18:46:06

I think that you need a measure like this:

Negative Department Count =
CALCULATE (
    DISTINCTCOUNT ( Table[Department] ),
    FILTER ( Table, [Amount] < 0 )
)

It will count each distinct department with Amount less than zero.

Let me know if this helps.

DAX - counting rows of distinct parent in child table

1 Answers