locked
DAX Expression equivalent to COUNTIF RRS feed

  • Question

  • Hi Guys,

    I want to create a DAX expression equivalent to COUNTIF (the Column O in excel is Days Open Column in the PowerPivot). See below COUNTIF formulas as follows: i.e. 1 Week Age (0-7 Days), 1 Month Age (8-30 Days), 3 Months Age (31-90 Days), 6 Months Age (91-180), More than 6 Months Age (>=181)

    1 Week Age =COUNTIFS('Quality Violation Log'!$O$9:$O$84,">=0",'Quality Violation Log'!$O$9:$O$84,"<=7",'Quality Violation Log'!P9:P84,"OPEN")

    2 Weeks Age =COUNTIFS('Quality Violation Log'!$O$9:$O$84,">=8",'Quality Violation Log'!$O$9:$O$84,"<=30",'Quality Violation Log'!P9:P84,"OPEN")

    Measure_1:=COUNTROWS(Filter(Table,Table[Days Open]>=0,Table,Table[Days Open]>=7)), I don't even know if I have the DAX expression correct! I was hoping that the Measure_1 will count the total rows in the "Days Open" column range 0 to 7 days.

    Then, I will to create another measure which will count the total of OPEN items based on another column. This is the DAX expression I was planing to do: 1_Week:=CALCULATE([Measure_1],Table[NCR Status]="OPEN")

    This DAX expression is only counting above 181 Days "More_than_6M:=COUNTROWS(Filter(Table,Table[Days Open]>=181))" and to Determined the number of OPEN I have this Measure: More than 6 Months Age:=CALCULATE([More_than_6M],Table[NCR Status]="OPEN")

    It will be better if I will be able to have all these calculation in one DAX expression.

    Any advise!

    Thanks!

    Monday, May 16, 2016 9:37 AM

Answers

  • Try something like this:

    Counter = CALCULATE(COUNTROWS(DatesTable),FILTER(DatesTable,DatesTable[DaysPast]>130),FILTER(DatesTable,DatesTable[DaysPast]<135))
    


    Monday, May 16, 2016 12:31 PM
    • Marked as answer by Mielkew Tuesday, May 17, 2016 5:00 AM
    • Unmarked as answer by Mielkew Tuesday, May 17, 2016 5:02 AM
    • Marked as answer by Mielkew Tuesday, May 17, 2016 5:41 AM
    Monday, May 16, 2016 10:53 PM
  • Try something like this:

    Counter = CALCULATE(COUNTROWS(DatesTable),FILTER(DatesTable,DatesTable[DaysPast]>130),FILTER(DatesTable,DatesTable[DaysPast]<135))
    


    Thanks Greg, this is exactly what I'm looking for.. Great Help!

    Although, I have another problem with my PowerPivot table the table is not updating the new data that I recently entered in the excel table. Sorry, if I ask this question here, this probably has been discussed in another forum/inquiry of another newbie like me. I don't really have any idea about PowerPivot nor Database. I just love to learn new things and the fact that there's someone like you who's willing to share thus make it more interesting. Kudos to you!

    Going back to my question, I have three excel worksheet table in one workbook. From PowerPivot Tab, I did "Create Linked Table" for these three excel table (Thanks to Youtube) I have now three PowerPivot Table in PowerPivot Window all of these tables has relationship by Date (Table_1, Calendar_1 and Calendar_2) (Again Thanks to Youtube). This is were I will be needing your expertise!!!, after updating the data in the excel tables the recently added data are not showing in all PowerPivot Tables and the "Refresh" button is greyed-out. Again, I went back to excel and go to PowerPivot Tab and next to "Create Linked Table" there is another button called "Update All", I thought WOW this might be the solution...!!! to my surprise an error pop-up "The command cannot be completed. No tables were detected in the active Excel workbook that are linked to PowerPivot Tables"..... Are you kidding me??? I did link these tables earlier and now "No tables were detected in the active Excel workbook that are linked to PowerPivot Tables"

    Like I said, I love searching and googling things that I have no idea, but I guess this is something beyond for me to comprehend without understanding the "database" world. I found something like if your using OLAP Query there's no way I will be able to use the "Refresh" button etc.. But I have no single idea about it! I don't know if the below information will be of any help solving my PowerPivot dilemma.

    Connection Type: OLAP Query

    Connection string: Provider=MSOLAP.5;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;ConnectTo=11.0;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue

    Any update or information is very very much appreciated.

    Thanks,

    • Marked as answer by Mielkew Monday, May 23, 2016 4:11 AM
    Tuesday, May 17, 2016 5:40 AM

All replies

  • Try something like this:

    Counter = CALCULATE(COUNTROWS(DatesTable),FILTER(DatesTable,DatesTable[DaysPast]>130),FILTER(DatesTable,DatesTable[DaysPast]<135))
    


    Monday, May 16, 2016 12:31 PM
    • Marked as answer by Mielkew Tuesday, May 17, 2016 5:00 AM
    • Unmarked as answer by Mielkew Tuesday, May 17, 2016 5:02 AM
    • Marked as answer by Mielkew Tuesday, May 17, 2016 5:41 AM
    Monday, May 16, 2016 10:53 PM
  • Try something like this:

    Counter = CALCULATE(COUNTROWS(DatesTable),FILTER(DatesTable,DatesTable[DaysPast]>130),FILTER(DatesTable,DatesTable[DaysPast]<135))
    


    Thanks Greg, this is exactly what I'm looking for.. Great Help!

    Although, I have another problem with my PowerPivot table the table is not updating the new data that I recently entered in the excel table. Sorry, if I ask this question here, this probably has been discussed in another forum/inquiry of another newbie like me. I don't really have any idea about PowerPivot nor Database. I just love to learn new things and the fact that there's someone like you who's willing to share thus make it more interesting. Kudos to you!

    Going back to my question, I have three excel worksheet table in one workbook. From PowerPivot Tab, I did "Create Linked Table" for these three excel table (Thanks to Youtube) I have now three PowerPivot Table in PowerPivot Window all of these tables has relationship by Date (Table_1, Calendar_1 and Calendar_2) (Again Thanks to Youtube). This is were I will be needing your expertise!!!, after updating the data in the excel tables the recently added data are not showing in all PowerPivot Tables and the "Refresh" button is greyed-out. Again, I went back to excel and go to PowerPivot Tab and next to "Create Linked Table" there is another button called "Update All", I thought WOW this might be the solution...!!! to my surprise an error pop-up "The command cannot be completed. No tables were detected in the active Excel workbook that are linked to PowerPivot Tables"..... Are you kidding me??? I did link these tables earlier and now "No tables were detected in the active Excel workbook that are linked to PowerPivot Tables"

    Like I said, I love searching and googling things that I have no idea, but I guess this is something beyond for me to comprehend without understanding the "database" world. I found something like if your using OLAP Query there's no way I will be able to use the "Refresh" button etc.. But I have no single idea about it! I don't know if the below information will be of any help solving my PowerPivot dilemma.

    Connection Type: OLAP Query

    Connection string: Provider=MSOLAP.5;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;ConnectTo=11.0;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue

    Any update or information is very very much appreciated.

    Thanks,

    • Marked as answer by Mielkew Monday, May 23, 2016 4:11 AM
    Tuesday, May 17, 2016 5:40 AM
  • Excel 2010 with free PowerPivot Add-In.
    Compatible with Office 2013/2016 Pro Plus.
    http://www.mediafire.com/download/419i5qjy5ddpu42/05_16_16.xlsx
    http://www.mediafire.com/download/9fyxszbi0vyouy7/05_16_16.pdf


    Thanks Herbert, I will keep the file this might be handy in the future!
    Tuesday, May 17, 2016 5:43 AM