cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
amarris
Frequent Visitor

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 viual to filter to Mono-line policies. 

 

Essentially I have a table with all active policy data including the Customer Number.  I want to find how many policies are assigned to each Customer Number (as there can be multiple policies to each customer):

 

Customer Number           Policy Status      Policy Number 

1                                         A                       123

2                                         A                       124

1                                         C                       125

1                                         Q                       126

2                                         A                       127

4                                         Q                       128

3                                         A                        129

 

So in the above example the count of Customer 1 would be "3", Customer 2 would be "2" and Customer 3/4 would both be "1" : 

 

How can I return this count so I can use it as a filter on my viusal later?  Thank you! 

 

 

1 ACCEPTED SOLUTION
AilleryO
Memorable Member
Memorable Member

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 😉

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

Hi @amarris ,

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

AilleryO
Memorable Member
Memorable Member

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
Super User
Super User

Hi @amarris 

you can create a new calculated column 

COUNTROWS ( 

CALCULATETABLE (

Table,

ALLEXCEPT ( Table, Table[Policy Number] ),

)

)

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors