Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Count based on a columns particular value and this count should ignore any available filters

Hi,

 

I need to create a measure, which calcualtes the total count based on a particualr columns value. I know how to do this. However, when I use this meaure, I dont want its value to change if any filters are aplied. I think we haveto use ALL here but I dont how to use in this context.

 

This is my existing measure : 

Number of Open Requests =  COUNTROWS(FILTER('Report requests', 'Report requests'[Status]="Open"))

 

For ex. if there is a slicer on "Status" and a value other than Open is selected, I still wants this measure to show the count of Open requests.

 

Apprecaite if you can help me on this.

 

Thanks,

SN

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I think I didn't try enough before posting this. I managed to figure this out using the below :

    Number of Open Requests =  COUNTROWS(FILTER(ALL('Report requests'), 'Report requests'[Status]="Open"))

     

    However, the above measure ignores all the filters, which is not what I intended. I want only the Status filter to be ignored and lets say if there is a Year filter, that should be considered while calculating the measure values.

     

    • v-lili6-msft's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      hi, Anonymous

      You can try to this formula as below:

      Number of Open Requests = COUNTROWS(CALCULATETABLE('Report requests',ALL('Report requests'[Status]), 'Report requests'[Status]="Open"))

      Best Regards,

      Lin

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        LIn, Thanks for your help here. Your meaure is becoming blank when a value other than "Open" is selecrted  from the status slicer. As I mentioned, I want the Status filter to be ignored altogether.