Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
siil-itman
Frequent Visitor

Struggling to convert a countifs

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.

UserEmailCompanyState
J Amejame@somewhere.comSomewhereEnabled
S Pamspam@see.netSee NetDisabled
A Ronaron@see.netSee NetEnabled

 

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

 ABC
1CompanyActiveDisabled
2Somewhere=COUNTIFS(USER!C:C,$A2,USER!D:D,"No")=COUNTIFS(USER!C:C,$A2,USER!D:D,"Yes")
3See 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

 

1 ACCEPTED SOLUTION
vojtechsima
Memorable Member
Memorable Member

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:

vojtechsima_0-1653312027244.png

Or you can write measure like this:

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

vojtechsima_1-1653312087466.png

 

View solution in original post

3 REPLIES 3
siil-itman
Frequent Visitor

@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?

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_0-1653393516211.png

 

vojtechsima
Memorable Member
Memorable Member

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:

vojtechsima_0-1653312027244.png

Or you can write measure like this:

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

vojtechsima_1-1653312087466.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.