Forum Discussion

bullius's avatar
bullius
Helper V
9 years ago
Solved

Calculated table - rows not showing

Hi

 

I am trying to produce a simple table with disctinct values, but the rows are not showing:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

There must be a simple explanation... any help welcome.

Need Help
  • bullius's avatar
    bullius
    9 years ago

    Thanks for all the replies.

     

    I have solved the problem this time, but still don't completely understand it:

     

    v-ljerr-msft, I checked the DISTINCT function with the other columns in the same table and found that it worked with all the columns that did not have blank or null values. For the columns that did have blank or null values (including the RegionName column), it displayed the table without showing the rows, but showed the number of rows at the bottom.

     

    Solution: I filtered out the blank values in the RegionName column and it worked fine after that.

15 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    9 years ago

    bullius DISTINCT function returns a one-column table so instead of calculated column you need to create a calculated table and it will work. Alternatively if you provide what you're trying to achieve I can tell of you of function that you need to use.

    • bullius's avatar
      bullius
      Helper V
      9 years ago

      Hi ankitpatira,

       

      Thanks for the reply. I clicked "New Table", then entered the above formula. Is that not creating a calculated table? I am trying to create a table of distint region names from a data table with a list of companies and their relative regions.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        bullius your formula is correct and it works just fine in my test. Your screenshot claims that the table has 12 rows, but for some reason they are not displaying. Have you tried closing and reopening Power BI, or refreshing your data?

  • lalthan's avatar
    lalthan
    Resolver II
    9 years ago

    bullius Try any of the below formula

     

    Option 1 - Regions = summarize(vwCompanies,vwCompanies[RegionName])

     

    Option 2 - Regions =  summarizecolumns(vwCompanies[RegionName])

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    bullius

     

    I also tested it on my side and the DISTINCT function works all fine for me. So the issue may related to your data table. Have you tried to use DISTINCT function with other columns(within the same table vwCompanies or other tables) to see whether the same issue happens? 

     

    Could you post your table structure and some sample data which can reproduce this issue in your case? It's better to upload a pbix file.

     

    Regards

    • bullius's avatar
      bullius
      Helper V
      9 years ago

      Thanks for all the replies.

       

      I have solved the problem this time, but still don't completely understand it:

       

      v-ljerr-msft, I checked the DISTINCT function with the other columns in the same table and found that it worked with all the columns that did not have blank or null values. For the columns that did have blank or null values (including the RegionName column), it displayed the table without showing the rows, but showed the number of rows at the bottom.

       

      Solution: I filtered out the blank values in the RegionName column and it worked fine after that.

      • dedelman_clng's avatar
        dedelman_clng
        Community Champion
        9 years ago

        IMO this should be reported as a bug.  Blank (and sometimes NULL) are valid data values, and we should be able to see the results without having to create a table visual in the report pane.