locked
Countif: How to implement it ? two cases RRS feed

  • Question

  • Hi, in order to plan the migration of my computers to Windows 10, I am collecting every day data of all my AD Computers objects. These data is historical (accumulative) and I already have more than 100K therefore I need to use PowerPivot. For clarify the context of my formulas

    Column D: Computer name

    Column Y: Type of OS

    Column Z: If it is Enabled or Disabled

    NOTE: I am not sure if I should create the formulas as Calculate column (in the Data Model) or Measure (in the Pivot table)

     

    Case 1: Identify if the computer was Re-image: if this computer (D86872) exist before with a different OS (Y86871)

    =IF(COUNTIFS(D$2:D86871,D86872,Y$2:Y86871,Y86872)=0,"YES","NO")   

    Case 2: if the status (Disabled) has change since last collection (yesterday: rows 84221-86871) and the status today is ENABLED (Z86872=TRUE)

    =IF(AND(COUNTIFS(D$84221:D86871,D86872,Z$84221:Z86871,"FALSE")>0,Z86872=TRUE),"RE-ENABLED","NO")

    I get the the total of computer of that name but without detect when the OS is different.

    =CALCULATE(COUNTROWS(ComputerData), FILTER(ComputerData, ComputerData[Name]=[Name] && ComputerData[OS Type] =[OS Type]))

    I am starting with this tool and I will appreciate your advise

    Friday, September 7, 2018 5:43 PM

All replies