how to exclude filter in KPI

[abc_777]

Dear Concern, 

In power bi I have 3 KPI. they all changes when i select any date  but I want to keeep filter option in two KPI and another KPI one I just dont want to change filter value. 

SO meaning when date select two kpi wil change value accodring but one will not 

please assist

[amitchandak · Accepted solution]

@abc_777 ,

Try like

calculate([KPI3], removefilters(Date))

or

calculate([KPI3], removefilters(Date[Date]))

[ryan_mayu · Accepted solution]

@abc_777 

maybe you can try to change visual interactions

https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions

[Cres · Accepted solution]

Over-all Employees = 

     COUNTX(

                ALL(Table_Name),

                Table_Name[Employee_Name]

     )

//The ALL function shields this KPI from other slicer filters and other context filters.

[ryan_mayu · Accepted solution]

@abc_777 

maybe you can try to change visual interactions

https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions

[amitchandak · Accepted solution]

@abc_777 ,

Try like

calculate([KPI3], removefilters(Date))

or

calculate([KPI3], removefilters(Date[Date]))

[abc_777]

@ryan_mayu ,

when i do your suggession my KPI shows the maximum.

@amitchandak if you please explain more that would be great

what i want is I have KPI card show total number of total employee

and other two KPI show their monthly salary and average salary.

and I want to keep filter on to emploeyye monthly salary and average salary but dont want filter on number of  Employee

this is just an example. in qlik sense they can do via $ and 1 so same thing want to power BI

thanks

[ryan_mayu]

@abc_777 

the KPI will not be affected by the filters. Then you need to write the correct DAX to show your value or use visual filter to fix the filter selection.

[abc_777]

@ryan_mayu 

eazy reply. but need more clearity. anyways is there anyone can give more clear ans please

[ryan_mayu]

@abc_777

it's very difficult to provide detailed answer, unless you can provide sample data and expected output.

[abc_777]

here are 3 KPI among them first one January 2021 total customer need to be static and newly monthly customer and Newly daily cusotmer would change as per filter or slicer. 

there would be no effect to Total january customer.

I have tried Edit Intraction under format Menu but when i click None its not working (it shows total of value from beginning to today total customer) thats not I want. 

[ryan_mayu]

@abc_777 

have you tried add month column to visual filters and select jan only?

[abc_777]

yes

[v-yiruan-msft]

Hi @abc_777 ,

Could you please share some sample data(exclude sensitive data) and the Fields setting of Customer Count KPI which get total number of total employee in order to get good understand for your requirement and provide you a suitable solution? Please also provide the related formula if you apply any measure on that Customer Count KPI visual. Thank you.

In addition, please review the solution in the following thread and check whether they can help you resolve the problem.

Exclude Visual Filter in KPI for the Target

How to disable filters in Power BI

Best Regards

[abc_777]

Hi I have tried the second link 

How to disable filters in Power BI

but when I disable visual intraction that KPI gives me total value of customer even I have filtered 

I have but sensible data. cant seperate them.

but if you have table data and make 2 or 3 KPI and one slicer and try to keep one KPI out of filter then it changes data to whole lot and then stick. I dont know wheather I make things easy to understand or not. 

[abc_777]

Hi I have tried the second link 

How to disable filters in Power BI

Hi I have tried the second link 

How to disable filters in Power BI

but when I disable visual intraction that KPI gives me total value of customer even I have filtered 

Here is sample data for customer ID

I have 3 KPI-

1) Average new Customer Creation per day

2) Total Customer Count

3) Total Customer Count per month

I want 

2) Total Customer Count not to filter when change month from calendar slicer.  but other two can change as per month

Customer ID
100072
100073
100071
100080
100081
100083
100084
100086
100087
100012
100011

[v-yiruan-msft]

Hi @abc_777 ，

Please try to change the formula of measure which applied on 2nd KPI visual as below and check whether it can get the correct result:

Total Customer Count = CALCULATE ( DISTINCTCOUNT ( 'Table'[Customer ID] ), ALL ( 'Table' ) )

Best Regards

[abc_777]

hi @v-yiruan-msft 

perfect its working. total number of customer is showing and not filtering

what if I want to filter then remove filter? need help for the syntax please

like all the value that starts with 'CR" and will stick that value.

CR test = COUNTROWS(

FILTER('table', left('column,2) = "CR")), All( ( 'table')))

[v-yiruan-msft]

Hi @abc_777 ,

You can update the formula of your measure [CR test] as follow and check whether it can get your desired result. Any comment or problem, please feel free to let me know.

CR test = COUNTROWS ( FILTER ( ALL ( 'table' ), LEFT ( 'table'[column], 2 ) = "CR" ) )

And for your another thread: how to count values starts with RR, my colleague(@Eyelyn9) is responsible for it. She has already replied you, please check.

Best Regardsd

[abc_777]

@v-yiruan-msft 

With following formulas

CR test = COUNTROWS ( FILTER ( ALL ( 'table' ), LEFT ( 'table'[column], 2 ) = "CR" ) )

and 

Total Customer Count = CALCULATE ( DISTINCTCOUNT ( 'Table'[Customer ID] ), ALL ( 'Table' ) )

showing total CR and total Customer count which is ok

but my requirement

in these formula I want to remore a part of syntax and then select month (January, or March or april) and then again give that syntax back so in KPI it will show me that specific month total value and will not change.

I make you more clear. say you select March and it show total number of cusotmer of March on that KPI and then stick that KPI and then when select April so other KPI will change but not this total number of cusotmer of March KPI as it is stick/fix to March

Again remove the syntax (that I need to know) then select Month May and it show total customer of May and I put syntax back so it hold May Total Cusotmer Data on this KPI and when I change to June other KPI will change but not this MAY Total Customer Data KPI

[v-yiruan-msft]

Hi @abc_777 ，

I‘m not clear about your requirement. Do you want to keep the static filter on KPI with total number of customer? If yes, please check whether the formula can achieve your requirement. 

Note: The month name in the following formula need to be changed manually every time.

Total Customer Count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer ID] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Month name] = 'March' )
)

If my understanding is wrong, please correct me. Any comment or problem, please feel free to let me know.

Best Regards

[abc_777]

Hi @v-yiruan-msft 

we are almost there.

I want to filter 

CR test = COUNTROWS ( FILTER ( ALL ( 'table' ), LEFT ( 'table'[column], 2 ) = "CR" ) )

and 

Total Customer Count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer ID] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Month name] = 'March' )

something like following . This is syntax error.  please correct where I can put && for "CR" and "April"

CALCULATE (

DISTINCTCOUNT ( 'Table'[Column]),

FILTER ( ALL ( Table), Column= "April")

)

&&

COUNTROWS ( FILTER ( ALL('Table'), (LEFT ('Table'[Column], 2 ) = "CR" ) ))

or

CR Count Test =

CALCULATE (

DISTINCTCOUNT ( 'Table'[Column]),

FILTER ( ALL ( Table), Column= "April")

&&

( 'Table'[Column] ) = "CR"

)

[v-yiruan-msft]

Hi @abc_777 ,

Please update the formula of your measure as below:

CR Count Test =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Column] ),
    FILTER (
        ALL ( Table ),
        'Table'[Column] = "April"
            && LEFT ( 'Table'[Column], 2 ) = "CR"
    )
)

Any comment or problem, please feel free to let me know.

Best Regards