Forum Discussion

Sylvain74's avatar
Sylvain74
Helper III
6 years ago
Solved

Incorrect count rows with slicer

Hi Everyone,

 

I have a data model with a single table, then I create a report with a table showing all the records. I added some slicers to filter the data displayed in the table. Finally I created a CountRows measure to show the number of rows in the table.

 

When I don't slice, the CountRows measure returns the correct number of rows, but when I use slicer, the value returns by the measure becomes incorrect. 

I'm quite new in Power BI and I understand my mistake. Can you please help me to solve it?

Thanks.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Sylvain74,

    Power bi visuals will auto aggregate records based on current category fields.

    For your scenario, it seems like your table contains records with the same category fields and they have been aggregated on table visual.  I'd like to suggest you do summarize on your table to calculate aggregate table rows.

    CountRows =
COUNTROWS (
    SUMMARIZE (
        ALLSELECTED ( HistoricalMatching ),
        [SalesDate],
        [SalesContractReference],
        [Customer]
    )
)

    Regards,
    Xiaoxin Sheng

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, 

    could screenshot your data (or provide a sample if the data is sensitive), so we can see why the result appears to be incorrect? 

  • Sylvain74's avatar
    Sylvain74
    Helper III
    6 years ago

    In the below print-screen, I have filtered on a specific customer using the Customer slicer, the CountRows card shows 18 rows but as you can see in the table, there is only 6 rows.

    My measure is created as follow: 

    CountRows = COUNTROWS(HistoricalMatching)
     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI Sylvain74,

      Power bi visuals will auto aggregate records based on current category fields.

      For your scenario, it seems like your table contains records with the same category fields and they have been aggregated on table visual.  I'd like to suggest you do summarize on your table to calculate aggregate table rows.

      CountRows =
COUNTROWS (
    SUMMARIZE (
        ALLSELECTED ( HistoricalMatching ),
        [SalesDate],
        [SalesContractReference],
        [Customer]
    )
)

      Regards,
      Xiaoxin Sheng

      • Sylvain74's avatar
        Sylvain74
        Helper III
        6 years ago

        Hi Xiaoxin,

         

        Thank you very much for your help. I didn't know at all the Power BI visuals automatically aggregate records. It allows me to discover that during the data importation, some rows were duplicated.

         

        Is it possible to disable or parametrize this auto aggregation?