Forum Discussion
Solution Specialisthow to exclude filter in KPI
abc_777 ,
Try like
calculate([KPI3], removefilters(Date))
or
calculate([KPI3], removefilters(Date[Date]))
maybe you can try to change visual interactions
https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions
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.
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
Solution SpecialistAnonymous
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
- abc_777
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.
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 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"
CALCULATE (DISTINCTCOUNT ( 'Table'[Column]),FILTER ( ALL ( Table), Column= "April"))&&COUNTROWS ( FILTER ( ALL('Table'), (LEFT ('Table'[Column], 2 ) = "CR" ) ))orCR Count Test =CALCULATE (DISTINCTCOUNT ( 'Table'[Column]),FILTER ( ALL ( Table), Column= "April")&&( 'Table'[Column] ) = "CR") 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
- abc_777
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:
CR Count Test =
CALCULATE (
DISTINCTCOUNT ( 'bm_all_cul bm_sales_dhk_all_cul'[Invoice] ),
FILTER ( ALL ( BMCalendar ), BMCalendar[MonthName] = "April" ),
FILTER (
ALL ( 'bm_all_cul bm_sales_dhk_all_cul' ),
LEFT ( 'bm_all_cul bm_sales_dhk_all_cul'[Column], 2 ) = "CR"
)
)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
- abc_777
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 MarchCR 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 samefor both of these measure CR is filtered by measure and top one using Calendar Slicer and bottom one using Measure. - abc_777
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