Aggregating at two different levels on a hierarchical dimension

[Anonymous]

I have a bit of a tricky one. 

Just to be clear, I am in DirectQuery mode on a Tabluar SSAS instance, so measures are my only option.

I have a fact table Log, and a related dimension table Application and then a parent dimension table ApplicationGroup.  Log has an Errors field which can only be 1 or 0.  I chose this way to represent the data instead of a boolean because it allows me then to run numeric aggregations, such as SUM(Log[Errors]) and that gives me how many rows in the Log contains errors (not sure if there is a better way to handle that?).

So the problem is this: I want to be able to count for each Application Group how many of its Applications have any errors for any particular filter I run on the data, be that a time filter or error type, etc.

At first I thought, add a measure, ApplicationsErrored, to Applications:

IF(SUM(Log[Errors]) > 0, 1, 0)

This gives me for each Application a 1 or a 0 for each application, indicating whether or not that application has an error.  Note, I am not looking for a sum of the errors for an application, my end goal is to get the sum of the applications containing errors sliced by application group.  Like this;

ApplicationGroup  SUM of ApplicationsErrored

A                            5

B                            3

C                            12

However, if I then try to aggregate ApplicationErrors by ApplicationGroup it just gives me a 1 or a 0, and does not even give me the option to run a further aggregation operation on the measure.  So, it just seems that despite my measure being part of the Application table, in fact, it seems in Power BI a measure is run independently of the table it is declared in.

To be clear also I can't create a calculated column on the Application table that has a 1 or 0 there depending on whether the applciation has any errors, because that only relates to any errors of any type ever.  Whereas I need to be able to use the filtering capabilities of Power BI such that it shows an application as errored for a particular date range, but perhaps not for another.

Any DAX wizards out there give a newbie a helping hand on this?

[v-chuncz-msft · Accepted solution]

@Anonymous ,

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Need-count-for-matrix-non-zero-values/m-p/406361#M186090

[Stachu]

try something like this (I'm assuiming you have single direction 1:many relationships between the tables)

Measure =
VAR __tab =
    GROUPBY (
        'Log',
        ApplicationGroup[Group],
        Applications[App],
        "Errors", SUMX ( CURRENTGROUP (), 'Log'[Error] )
    )
RETURN
    SUMX ( __tab, [Errors] )