Forum Discussion
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...
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 😉
tamerj1
Community Champion
Community ChampionHi Anonymous
you can create a new calculated column
COUNTROWS (
CALCULATETABLE (
Table,
ALLEXCEPT ( Table, Table[Policy Number] ),
)
)