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.
bullius I have no idea why you're still getting a blank result. And it still says 12 values at the bottom of the screen, which is weird. I can't reproduce what you're seeing. I think I'd have to see the source table you're trying to filter from. There's got to be something about that table structure that's causing this.
BrightMoon if you only want that single column, there is no need for the FILTER statement. DISTINCT() returns distinct rows from a table or column. FILTER() returns a filtered subset of all columns of a table. It doesn't return a single column, so DISTINCT will return distinct complete rows of that filtered table. And the statement FILTER(Tablename, Tablename[Columnname]) does not contain any filtering criteria for Columnname, so it won't filter out any rows.
If translated back to English, your formula would read something like "Return distinct complete rows from Tablename, which is probably every row unless there are two rows where every ssingle column has the exact same value, but first filter Tablename to only rows where Columnname exists, which is every row because every column exists on every row of any table, whether it has values or not." You might as well write Newtable = Oldtable because the results would be the same.
See my previous response if you want a single column of distinct values. If you want to include a null value, use either NewTable = VALUES(Tablename[Columnname] or NewTable = SUMMARIZE(TableName, TableName[ColumnName]). If you only want non-null values, NewTable = DISTINCT(Tablename[Columnname])