Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 .
Best Regards.
Community Support Team _ Eason
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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?