Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to count Matching values in One Column based on values in another Column

Good Morning,    I am trying to determine how I can get a count of a matching data fields within 1 column based on another column in order to eventually use it down the line as as a filter in my vi...
  • AilleryO's avatar
    AilleryO
    4 years ago

    Hi,

     

    If you want your count by Customer and not by Policy, then I think it should be a calculated column as advised by Tamerj1, but with this :

    Number Policy = CALCULATE( COUNT(TabPolicy[Policy Number ]) ,
    ALLEXCEPT(TabPolicy ,TabPolicy[Customer Number] ) )
     
    Or as a measure it would be :
    Count of Policy = 

    VAR CurrentCustomer = SELECTEDVALUE( TabPolicy[Customer Number] )

    RETURN

    CALCULATE( COUNT( TabPolicy[Policy Number ] ) , TabPolicy[Customer Number] = CurrentCustomer )
    But it will not be as easy to filter as a column.
    Another solution would be to do a pre consolidated table :
    Table Number of Policy = 

    SUMMARIZECOLUMNS(TabPolicy[Customer Number] , "Count Policy" ,

    COUNT(TabPolicy[Policy Number ] ) )
     
    Let us know which solution you'll adopt 😉
v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
4 years ago

Hi Anonymous ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj