locked
How to count rows where a specific value exist? RRS feed

  • Question

  • This is primarily  a question for DAX/Power BI. I'm trying to figure out how to count rows for a particular column where only a specific value exist. For instance, I'm trying to count how many times a certain field contains a true value or false value across multiple columns. 

    I found this formula here: =CALCULATE(COUNTROWS('table'), ALLEXCEPT('table', 'table'[n_country])) from 

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/725597f2-aa4c-4154-ad26-b24319d2758f/dax-expression-to-count-values-within-a-table

    however, I'm not quite sure how to adapt it to count only rows where a value is equal to True, False, 17, "foobar", etc...

    Thanks, I'm new to DAX!


    Wednesday, May 20, 2015 3:15 PM

Answers

  • Hi Taylor,

    If you want to return a count of the rows after applying a filter on a single column then you could do something along these lines:

    CALCULATE(
      COUNTROWS(Table),
      Table[Column1] = TRUE 
    ) 

    If you want to return a count of the rows after applying a filter on multiple columns then you could do something like this:

    CALCULATE(
      COUNTROWS(Table),
        Table[Column1] = TRUE,
        Table[Column2] = FALSE 
    )

    Note that the above will apply the filtering with AND logic.

    If you need the column filters to be evaluated with OR logic then you can do something like this:

    CALCULATE( COUNTROWS(Table), FILTER( Table, Table[Column1] = TRUE || Table[Column2] = FALSE )

    )



    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    • Marked as answer by Taylor Brockhoeft Wednesday, May 20, 2015 5:12 PM
    • Edited by Michael Amadi Wednesday, May 20, 2015 5:44 PM Minor tweaks to DAX syntax
    Wednesday, May 20, 2015 4:26 PM

All replies

  • Hi Taylor,

    If you want to return a count of the rows after applying a filter on a single column then you could do something along these lines:

    CALCULATE(
      COUNTROWS(Table),
      Table[Column1] = TRUE 
    ) 

    If you want to return a count of the rows after applying a filter on multiple columns then you could do something like this:

    CALCULATE(
      COUNTROWS(Table),
        Table[Column1] = TRUE,
        Table[Column2] = FALSE 
    )

    Note that the above will apply the filtering with AND logic.

    If you need the column filters to be evaluated with OR logic then you can do something like this:

    CALCULATE( COUNTROWS(Table), FILTER( Table, Table[Column1] = TRUE || Table[Column2] = FALSE )

    )



    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    • Marked as answer by Taylor Brockhoeft Wednesday, May 20, 2015 5:12 PM
    • Edited by Michael Amadi Wednesday, May 20, 2015 5:44 PM Minor tweaks to DAX syntax
    Wednesday, May 20, 2015 4:26 PM
  • Thanks so much Michael!
    Wednesday, May 20, 2015 5:13 PM
  • No problem :)

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Wednesday, May 20, 2015 5:37 PM
  • Just noticed that there is a trailing ')' on each snippit
    Wednesday, May 20, 2015 5:39 PM
  • Good spot. Sorted ;)


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Wednesday, May 20, 2015 5:44 PM