Forum Discussion

senright1989's avatar
senright1989
New Member
9 years ago
Solved

Counting unique values in a column based on numerous values

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.

  • Anonymous's avatar
    Anonymous
    9 years ago

    Something like this?

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Something like this?

    Occurs = 
VAR PayItem = [Pay Item #]
RETURN
CALCULATE(
	countrows('YourTable'),
	ALL('YourTable'),
	'YourTable'[Pay Item #] = PayItem
)
    • senright1989's avatar
      senright1989
      New Member
      9 years ago

      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.

       

       

       

       

  • kaushikd's avatar
    kaushikd
    Icon for Resolver II rankResolver II
    9 years ago

    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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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