Forum Discussion
Create a Measure to Count Number of Distinct Values That Have the Same Value in Another Column
Hello,
I have call data and based on the data I need to know how many seperate units (Column: Responding Unit Numbers) were assigned to each call (Column: Call Number). Yes, the call numbers in my data will duplicate and create an additional row of data with the same call number if there is more than one value (such as the assigned unit number) assigned to a column. But I simply cannot just count the number of times my call numbers appear as duplicates in the dataset as a whole because my data also has other columns that sometimes produce more than one value also, causing the call number to multiply into further rows, so doing this would over-count the number of units I am trying to count for each call.
How can I create a measure that counts the number of different values (unit numbers) that are assigned to each call number in my dataset? I think I need this to be a measure and not its own column because my ultimate goal with the amount of units per call is to use these numbers as the basis for my bubble size on my map visual of call locations so the user can easily identify where the calls with the largest unit responses were located.
Here is a sample of my data with the call numbers and the individual units that responded to each of thosed calls that I need to count for each distinct call number: https://docs.google.com/spreadsheets/d/13KbnAd4bE608GRsNWGWcQZlFAlAR2dRq/edit?usp=sharing&ouid=109601330790345098112&rtpof=true&sd=true
I appreacite any help! Thank you so kindly!
Ashley
1 Reply
- AnonymousNot applicable
Hi ClackamasAshley ,
I used some of your sample data and you can check the results below:
Total = var _t=CALCULATETABLE(VALUES('Table'[Responding Unit Numbers]),ALLEXCEPT('Table','Table'[Call Number])) RETURN COUNTX(_t,[Responding Unit Numbers])An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.