Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count Rows Function works only for one category

Hi all, I have an appended query where all the models of 5 products are listed in the [Model] column with other columns like [Update Date]. I have created a count function that counts the same Models by same Update Date. 

 

Count = CALCULATE( COUNT('Group'[Model]), ALLEXCEPT('Group','Group'[Update Date], 'Group'[Model]))
 

To show the results, I use a table with the category name, the model, and the due date. 

The count function (which is a measure created in the appended query) works only for one category, for example it works for bicycles and not for e-bikes. It means that the count is right for bicycles and not for the e-bikes. See below the problem, same model for e-bikes and same update date but they are not grouped together like the bicycles.

 

CategoryModelUpdate DateCount
BicycleRoss31/12/20211
BicycleGas28/12/20212
BicycleRino31/12/20211
e-bikesGogo12/12/20212
e-bikesGogo12/12/20212

 

For each rows, if I select one, it displays the serial number and additional infos in another table. If the count is 2 per same model and same Update Date, it shows two rows in the other tables. 

The product categories are five in total, but in the table filter I have selected bicycles and e-bikes, which are the only products that I want to check the Update Date. 

Why the count works for one category and not for the other? I have tried to change the measures many times, but I have not found a working solution.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    I solved the issue: in the table with the category, model, Update Date, and Count, I had the serial number column. By remove it, the count works. I think that the serial number column was filtering in the measure in some way. Thanks for your support.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I solved the issue: in the table with the category, model, Update Date, and Count, I had the serial number column. By remove it, the count works. I think that the serial number column was filtering in the measure in some way. Thanks for your support.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , what is the expected output?

     

    If this is a column try like

     


    Count = CALCULATE( COUNT('Group'[Model]), filter('Group','Group'[Update Date] = earlier([Update Date] ) && 'Group'[Model] = earlier('Group'[Model])))

     

    if measure , try like

    Count = CALCULATE( COUNT('Group'[Model]), filter(allselected('Group'),'Group'[Update Date] = max([Update Date] ) && 'Group'[Model] = max('Group'[Model])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for your help. I have tried the measure you posted and the result is the same: it works for the bycicle category and not for the e-bikes category. 

       

      The expected output is like the table in the first post with the correct e-bikes count per same model and same Update date. The table shows that there are 2 e-bikes with Go-go model and 12/12/2021 update date, the count is right but they are not grouped in one row.

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        Anonymous , if the date does not have timestamp, in table visual both rows should merge. Even for simple count like below example

         

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      How can I upload the sample data? I cannot find any insert file button in the reply message box. sorry.