Counting unique values in a column based on numerous values

[senright1989]

Hi All, 

I have searched through this forum for a few hours today and for some reason i couldn't get what i am trying to do to work. 

I have the table called "Sample".

I am looking to create a column where get a count of the number of times the pay item is entered in the table. For example pay item 1 would be 4. Pay item 2 would be 3. Pay item 3 would be 1. 

Then using this value I would be able to get the price per day for each entry instead of a total cost per each pay item. 

Any help would be appreciated with this.

[v-jiascu-msft]

Hi @senright1989,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[kaushikd]

@senright1989

Hi for data refference i have taken your data( #Sample)

what your requirement is can be easily impleamented using a measure.

Step 1. R.click on the table and click New Measure

Step 2. Use this dax to calculate the count of Pay Item Count of Item = Count('Sample'[Pay Item])

Step 3. In the report page drop a table and select Date---Pay Item---Count of Item as the fields.

Please have a look what i have done here.

If this is what you are looking for the please don't forget to mark this as a solution.

--Regards

   Kaushik

[Anonymous · Accepted solution]

Something like this?

Occurs = 
VAR PayItem = [Pay Item #]
RETURN
CALCULATE(
	countrows('YourTable'),
	ALL('YourTable'),
	'YourTable'[Pay Item #] = PayItem
)

[senright1989]

Thanks Ross, sorry for getting back so late but i will accept yor solution because I was able to use it in the end to get the result apart from one tweak.

Occurs =
VAR PayItem = MIN(Sheet1[Pay Item #])
RETURN
CALCULATE(
 countrows(Sheet1),
 ALL(Sheet1),
 Sheet1[Pay Item #] = PayItem
)

I had to add "MIN" because i was getting the following error:

A single value for column 'Pay Item #' in table 'Sheet1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I ended up using a much slower method intitially to get the job done for the interim. My co-worker showed me how to add an index column and group by the pay item with the countrows function in the query editor. For this to work i had to copy my whole data set and then merge it back in by adding columns. This worked fine but in the end it doubled my dataset and the load times were significantly increased.

Thanks again.