ssasdaxssas-tabular

DAX - counting rows of distinct parent in child table


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:

  1. Creating a separate table listing only the departments, adding a calculated column aggregating the amounts from each child, and checking if the result < 0.
  2. 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.


Solution

  • 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.