Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Count with Multiple Conditions and Multiple Column Referenced

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!
Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago
    Try using CALCULATETABLE and put in your conditions then use COUNTROWS to count the resulting table.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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,

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

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