PBI does not count items (rows) from list on Sharepoint correctly

[Libor]

Hi,

I would like to ask for help with right displaying count from data placed in a Sharepoint list (connection between PBI and Sharepoint).

There are 39 items in the list on Sharepoint, but the ID starts on the number 18 (ID from 18 to 56). Previous 17 items were for testing - fields, notifications etc. These items have been deleted and I do not see them anymore in the list on Sharepoint.

If I try to add these items to the table in the PBI, the total count of IDs shows the number 56, but there is only 39 items (and some items are also excluded). Weird is, that if I add a new column with IDs without counting next to the IDs column with counting, it is displayed more than one ID in some items, but the ID name is still only one (picture below).

Could anyone help me with it? Do I something wrong?

Thank you in advance.

Libor

[v-easonf-msft]

Hi, @Libor 

It  has nothing to do with whether id is unique for each row in your current ‘table visual‘(It needs to take into account all the data in your original table).
You can  refer to this  document about work with aggregates . 

• Count (Not Blanks): Counts the number of values in that field that aren't blank.
• Count (Distinct): Counts the number of different values in that field

Best Regards.
Community Support Team _ Eason

[Libor]

Hi,

thank you for your explaination. Now it is clear.

But have to say, that my problem was in the data itself. We have a one column with allow multiple selection in the sharepoint. In some items are more than one records in this column and after loading these items to the PBI, all of these items were splited to more items based on the number of records in the column with multiple selection. (item with 3 records were splited to 3 items with same ID).

Thank you all for ideas and information.

[mahoneypat · Accepted solution]

It looks like the default aggregation on your ID column is Max.  Change it to count or distinctcount in the field well (pull down on the arrow for that column).

Regards,

Pat

[Libor]

Hi,

thank you for your idea. The aggregation was set as Count. I have changed it to the Distinctcount and that looks great. But I am not sure what is the diferent between count and dictinctcount in this situation.

I tought that ID is unique for each row. So the count and distinctcount should shows the same result.

Am I right?