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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
Resident Rockstar
Resident Rockstar

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
Resident Rockstar
Resident Rockstar

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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