Count with Multiple Conditions and Multiple Column Referenced

[Anonymous]

Hello,

I'm currently stumped on a DAX formula, which I need to count the number of entries in a column (let's call it Rule).

I need it to be filtered for the following conditions which are contained in this same table already (table 1);

- [Status] = "Active"

- [Condition] = "Performing As Expected"

It also need to filter for another condition, contained in another table (table 2), which has a relationship to table 1

- [PercentageBin] = "81-90%" or [PercentageBin] = "91-100%"

I want this count to be able to be shown in a Card.

Currently, I have the following DAX, but the Card is showing as (Blank):

Count of Rule for Active =
             CALCULATE(
                COUNT('Table1'[Rule]),
                  FILTER('Table1',
                           'Table1'[Status] = "Active" && 'Table1'[Condition] = "Performing As Expected"),
                  FILTER('Table2',
                           'Table2'[PercentageBin] = "81-90%" || 'Table2'[PercentageBin] = "91-100%")
)

Any help would be greatly appreciated!

[Ashish_Mathur]

Hi,

Does this measure work?

=CALCULATE(COUNT('Table1'[Rule]),'Table1'[Status] = "Active",'Table1'[Condition] = "Performing As Expected"),FILTER('Table2','Table2'[PercentageBin] = "81-90%" || 'Table2'[PercentageBin] = "91-100%"))

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[Greg_Deckler]

Try using CALCULATETABLE and put in your conditions then use COUNTROWS to count the resulting table.

[Anonymous]

Hi Greg,

How can I use CALCULATETABLE to reference a column in a different table? I can only get CALCULATETABLE to reference Table1, but not Table 2.

[Anonymous]

I've now tried the following:

Count of Rule for Active = 
COUNTROWS (
    CALCULATETABLE ('Table1',
    FILTER('Table1',
        'Table1'[Status] = "Active"),
    FILTER('Table1',
        'Table1'[Condition] = "Perfoming As Expected"),
    FILTER('Table2',
        OR('Table2'[PercentageBin] = "81-90%",
            'Table2'[PercentageBin] = "91-100%")
        )
    )
)

Still showing (Blank) when I dragged this Measure into a Card.

I'm not if this affects it, but I also have other columns like [Region] and [Country] in Table 1, which I have Slicer created for. Do I need to adjust my DAX to include reference to [Country] and [Region]?

[v-lid-msft]

Hi @Anonymous ,

Based on my test, your formula works fine on our sample table, could you please describe the relationship between two tables if it does not contain any confidential information?

Best regards,