Struggling to convert a countifs

[siil-itman]

I have a spreadsheet that, amongst other things, it contains a dump of our Active directory users and the current state of their account (active/disabled). The spreadsheet isn't easy to manage due to the volume of data vrom so many different sources so I thought of trying Power BI.

After linking wiht our AD, I've got a table that shows the user data "full name", "email" and "company" (we have a lot of different companies) plus a calculated column "state" based on the UAC code that indicates if the account is Enabled or Disabled.

User	Email	Company	State
J Ame	jame@somewhere.com	Somewhere	Enabled
S Pam	spam@see.net	See Net	Disabled
A Ron	aron@see.net	See Net	Enabled

I now need to be able to show a summary table that lists the number of active and disabled users per company
In excel it would be easy using the countifs function and refering to a column listing the companies

	A	B	C
1	Company	Active	Disabled
2	Somewhere	=COUNTIFS(USER!C:C,$A2,USER!D:D,"No")	=COUNTIFS(USER!C:C,$A2,USER!D:D,"Yes")
3	See Net	=COUNTIFS(USER!C:C,$A3,USER!D:D,"No")	=COUNTIFS(USER!C:C,$A3,USER!D:D,"Yes")

I've looked at the things like using 

COUNTX (
    FILTER (
        Table,
        Condition1 && condition2 && condition3 && howmanyconditionsthatyouwant      
           ),
    Table[ColumnToCount]
       )

and

   CALCULATE(
      COUNT([column]).
      FILTER(
         [table], 
         //your filter conditions
      )
   )

but I struggling with the filters and can't work out how to produce a table that lists the companies with the active/disable numbers using DAX.
Should I be creating a measure, a calculated column or a new table?

Thanks in advance

[siil-itman]

@vojtechsima The filtering I'm struggling with (having a bad day in work). How did you generate the "Count of Users in the two example tables? 

The measure produced some interesting results! The "State" column is a calculated column based on the value in the UAC column to display a human readable value

State = if([UAC]="512","Enabled",if([UAC]="66048", "Enabled-pne",if([UAC]="66050","Disabled-pne",if([UAC]="514","Disabled","Check me"))))

The countrows did a sum of the numeric value, not the displayed value! Is there a way around this?

[vojtechsima]

Hi, @siil-itman ,
Just put the field into Columns, right click on it and choose Count.
For the filtering, drag the wanted field into Filter Pane, click on it, choose Basic Filtering and then select your preferred value.

[vojtechsima · Accepted solution]

Hi, @siil-itman 
The easiest way is to use filter in Filter pane, then you have your desired information displayed, for example in Table visualization, like this:

Or you can write measure like this:

Activated = COUNTROWS(FILTER('Table', 'Table'[State] = "Enabled"))