Forum Discussion

ChrisC365's avatar
ChrisC365
Brass Contributor
Feb 25, 2020
Solved

PowerPivOt and DAX counter

Hi

 

I have a PowerPivot table, which consists of 3 tables in model which uses two main filters.  Filter one column name is called 'Funded', the values in this column are either 'Yes', 'No' or 'blank'.  The second filter is a column called 'Category', and in here the values can be either 'Yes', 'No' and 'blank'.  I was wanting to use DAX to count the number of rows that relate to the number of rows that are filtered based on the selection in column 'Funded' when either 'Yes' or 'no' is chosen.  So I can use DAX to count All these rows but I just want to show the count based on my results that are in the pivot table.  So for example if I selected 'yes' and 'no' on the 'Funder' filter, this will show 12 rows for 'yes' and '5' rows for no.  

 

Measure 1 for 'Yes' would read: 12,

Measure 2 for 'No' would read: 5

 

Another way or possible a better way of explain this, I am trying to do this '=COUNTIF(Range:Range, "Yes")' but with DAX instead.

 

Chris

 

BI & Data Analysis
  • ExcelExciting's avatar
    ExcelExciting
    Feb 25, 2020

    Hi ChrisC365 

     

    You can try this formula might be helpful

     

    MeasureCountYes:= COUNTROWS(FILTER(TableName,[ColumnName]="Yes"))

     

    MeasureCountNo:= COUNTROWS(FILTER(TableName,[ColumnName]="No"))

     

    If you are still facing issue please attached sample file to work on it and provide you appropriate solution, make sure to demonstrate your desired output in the sheets for better understanding.

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com

     

3 Replies

  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Feb 25, 2020

    Hi ChrisC365 

     

    You can try this formula might be helpful

     

    MeasureCountYes:= COUNTROWS(FILTER(TableName,[ColumnName]="Yes"))

     

    MeasureCountNo:= COUNTROWS(FILTER(TableName,[ColumnName]="No"))

     

    If you are still facing issue please attached sample file to work on it and provide you appropriate solution, make sure to demonstrate your desired output in the sheets for better understanding.

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com

     

    • ChrisC365's avatar
      ChrisC365
      Brass Contributor
      Feb 25, 2020
      Faraz
      Many thanks, looking at my worksheet there is more happening with the filtering than I initially detailed. Its not that easy to paste a copy of similar data. Your formula works fine, this has made me realise I need to do a little more work my side. I will accept this solution and return at later date if I need help again.