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.
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.
Solved! Go to Solution.
Hi,
This calculated column formula works
=CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Plant]=EARLIER(Table1[Plant])&&Table1[Type]=EARLIER(Table1[Type])))
Hope this helps.
Hi,
This calculated column formula works
=CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Plant]=EARLIER(Table1[Plant])&&Table1[Type]=EARLIER(Table1[Type])))
Hope this helps.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |