how to exclude filter in KPI

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

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. 

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

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:

Best Regards

hi Anonymous 

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.

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.

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

Anonymous 

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

Anonymous 

any update?

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.

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

Best Regards

Hi Anonymous 

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"

Hi abc_777 ,

Please update the formula of your measure as below:

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

Best Regards

Anonymous 

thanks for your reply. 

i am able to filter All upto calendar but not "CR"

when I type  left( 

telesense not show me 'bm_all_cul bm_sales_dhk_all_cul'[Invoice] table to type rest of the formula

('bm_all_cul bm_sales_dhk_all_cul'[Invoice],2) = "CR"

telesense only shows me all built in formula and measures that I was created previouslly. this is why I raised last thread. so what is my error how i can filter "April "as well as "CR"

CR Count Test =

    CALCULATE (

          DISTINCTCOUNT ( 'bm_all_cul bm_sales_dhk_all_cul'[Invoice]),

          FILTER (

                          ALL (BMCalendar),

                                          BMCalendar[MonthName]= "April"

               &&

                         left(sale

)

)

 

Hi abc_777 ,

Please update the formula of measure as below:

If the above one still not get the correct result, please share your sample pbix file(exclude sensitive data) with me in order to provide you a suitable solution later. Thank you.

Best Regards

Anonymous ,

now the formula is working. BUt dont know why the value comes difference.

If you have any idea.

CR Invoice Count = COUNTROWS ( FILTER (ALL( 'bm_all_cul bm_sales_dhk_all_cul' ), LEFT ( 'bm_all_cul bm_sales_dhk_all_cul'[Invoice], 2 ) = "CR" ) )

 

is gives me 26671 CR data for March

 

CR Count Test =

CALCULATE (

Count ( 'bm_all_cul bm_sales_dhk_all_cul'[Invoice] ),

FILTER ( ALL ( BMCalendar ), BMCalendar[MonthName] = "March" ),

FILTER (

ALL ( 'bm_all_cul bm_sales_dhk_all_cul' ),

LEFT ( 'bm_all_cul bm_sales_dhk_all_cul'[Invoice], 2 ) = "CR"

)

)

 

this gives me 2563 CR data for march.

 

not sure why. they should be same

 

for both of these measure CR is filtered by measure and top one using Calendar Slicer and bottom one using Measure. 

 

 

any update?

Hi abc_777 ,

Could you please share your sample pbix file with me by private message in order to make troubleshooting and provide a suitable solution? It is difficult to find the cause of problem without the data. Thank you.

Best Regards