Forum Discussion
Calculated table - rows not showing
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.
Hi bullius,
I met with the same issue and agrees with your solution to filter out the blank values first.
Can I ask you how to do this? I tried something like =DISTINCT(FILTER([table],[table.column])) but I got the full table, what should I do to get the column only with distinct values?
Thank you.
I filtered the column on Query Editor. However, this worked as well:
SUMMARIZE ( FILTER ( Table, [Column] <> BLANK() ), Table[Column] )
Anonymous, unfortunately I can't share the data, but it would be interesting to know what causes this issue in some tables and not others.
bulliusyeah if you can ever come up with a non-confidential table that produces the same results let me know.
Thanks bullius it works well.
To Anonymous Thank you for your reply. I am having the same issue with bullius - if I "DISTINCT" a column with blank value, I can not see the result list in the Data View. Since the number of rows is returned correctly at the bottom, I think it worked well in the backend and potentially a representation issue.
BrightMoon Have you tried the SUMMARIZE or VALUES methods yet?
Actually I just did it with a small test table and I can get the same results with DISTINCT even when there are blanks, so there must be something else going on. I really would need to see a sample data set that produces a blank table with DISTINCT, because I can't replicate that.
Test number 1 has only one blank row, but it works fine. This is the source table:
And here are the results of using DISTINCT and VALUES:
So I thought maybe the problem was that it couldn't decide what to do with multiple blank rows, but it turns out that's not the case. Here's the source table with multiple blanks on Column1:
And again both DISTINCT and VALUES work just fine:
So there must be something else going on here. I can't get it to fail the way you guys are all describing so there must be something different about the tables you're using. Until someone can show me a sample data set that produces the failure you're seeing I don't know what else to tell you.