cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

## Counting Pairs and displaying value next to them

I want to count how many times a specific pair of values occur and display the count next to them. How would I count a set of values and duplicate the count x number of times to line up with original value?

For this table the pairs would be in plant and type. So 'tree' & 'seed' show up 2 times, 'bush' & 'leaf' 3 times etc. How would I count that and then put a 2 next to each instance of tree and seed, and a 3 next to bush and leaf? Could I possibly make a new table with duplicates removed and then just count the pairs and put the total in the corresponding column? I want to be able to update the data and have it automatically recount the pairs so I don't know if making new tables is the best idea.

1 ACCEPTED SOLUTION
Super User

Hi,

This calculated column formula works

``=CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Plant]=EARLIER(Table1[Plant])&&Table1[Type]=EARLIER(Table1[Type])))``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
Super User

Hi,

This calculated column formula works

``=CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Plant]=EARLIER(Table1[Plant])&&Table1[Type]=EARLIER(Table1[Type])))``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resident Rockstar

@MulberyPie -
You can do as a column:

``````Column =
VAR __pair = TableName[Plant] & TableName[Type]
VAR Result =
CALCULATE(
COUNTROWS(TableName),
TableName[Plant] & TableName[Type] = __pair
)
RETURN Result``````

or as a measure:

``````Measure =
VAR __pair = SELECTEDVALUE(TableName[Plant]) & SELECTEDVALUE(TableName[Type])
VAR Result =
CALCULATE(
COUNTROWS(TableName),
TableName[Plant] & TableName[Type] = __pair
)
RETURN Result``````

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

I have a lot more columns than what is shown. When I use the column method it shows 1's next to every row, but when that column is put into a table with just the pair coulmns it shows a total, but that total has some wrong numbers. Some of the counts for the pairs are right, but some are too high.  I think that having extra columns that can vary with the pair may mess up the new column method.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors