Forum Discussion

lawada's avatar
lawada
Helper III
4 years ago
Solved

getting one extra blank row in the data when using count

i have a table that shows : laundry name, order id, and rating id . for one laundry I'm getting one extra blank row for the rating , for example:
one laundry has total orders =6 and total ratings =1 but i=in power bi its counting it as 6 orders and 2 ratings with one extra blank row for the rating

note that when i run the query in my database i get the count of rating as 1 but in power bi only ot's  adding one extra row

Need Help
Show and Tell

9 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    Hi,

    A few questions...

    1) Have you written a measure to do the count or are just dropping the column into the visual ? If it's a measure can you share? If you're dropping column into visual I'm assuming you've checked what aggregation it is set to use? (Will normally default to Sum rather than count).

     

    2) Is it possible a row looks blank but actually has something like a space in it?

     

    3) Is it all just a single table or do you have relationships involved? If you do can you share a picture of your data model or even better the pbix file (or demo version of it)

     

    Ben

     

    • lawada's avatar
      lawada
      Helper III
      4 years ago

      1)its just measure with count function

      2) i checked in the database and there's one value

      3)theres no relationship with other tables and .

      • bcdobbs's avatar
        bcdobbs
        Community Champion
        4 years ago

        Do you want to strip any sensitive data out and send your pbix file. Nothing in your answers is jumping out as the cause! Will happily take a look.

  • aj1973's avatar
    aj1973
    Community Champion
    4 years ago

    Hi lawada 

    You can go to the filter panel and deselect the blank for that rating_id field

     

    • lawada's avatar
      lawada
      Helper III
      4 years ago

      that's not possible if i want to count values, it will count the blank row however theres no blank value in the data

  • kate_kk's avatar
    kate_kk
    Regular Visitor
    3 years ago

    May I know any findings for this problem?
    Case 1:
    I have the same issue, checked there are only 41 rows in data, but after adding a relationship, 
    there is an extra row shown in Count() function,
    It also shows a blank option in the filter pane with no record.

    Case 2: 
    After applying and loading data in power query to power bi report, 

    both the report and data view show an extra blank record.

    but there is no record found in the data source and power query editor (filtered with null, result = 0)
    and no relationship was applied in the report.
    Checked another report with the same data source but no extra blank row problem.