Forum Discussion

abc_777's avatar
abc_777
Icon for Solution Specialist rankSolution Specialist
5 years ago
Solved

how to exclude filter in KPI

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 meani...
Need Help
abc_777's avatar
abc_777
Icon for Solution Specialist rankSolution Specialist
5 years ago

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" ) ))
 
or
 
CR Count Test =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Column]),
FILTER ( ALL ( Table), Column= "April")
&&
( 'Table'[Column] ) = "CR"
)
 
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    abc_777
    Icon for Solution Specialist rankSolution Specialist
    5 years ago

    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
    )
    )
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
    abc_777
    Icon for Solution Specialist rankSolution Specialist
    5 years ago

    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. 
     
     
  • abc_777's avatar
    abc_777
    Icon for Solution Specialist rankSolution Specialist
    5 years ago

    any update?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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