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.
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
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.
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.
dedelman_clng bullius it's not a bug; you're just using the wrong formula if you want to include null as a valid value. If you want to return null as a value in a table like this, use NewTable = VALUES(TableName[ColumnName]) or NewTable = SUMMARIZE(TableName, TableName[ColumnName]). Read the notes on the differences between VALUES and DISTINCT to understand why. https://msdn.microsoft.com/en-us/library/ee634943.aspx
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])
