Calculated table - rows not showing

[bullius]

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.

[v-ljerr-msft]

@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 · Accepted solution]

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.

[BrightMoon]

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]

@BrightMoon,

I filtered the column on Query Editor. However, this worked as well:

SUMMARIZE ( FILTER ( Table, [Column] <> BLANK() ), Table[Column] )

@KHorseman, unfortunately I can't share the data, but it would be interesting to know what causes this issue in some tables and not others.

[BrightMoon]

Thanks @bullius it works well.

To @KHorseman 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.

[KHorseman]

@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.

[KHorseman]

@bulliusyeah if you can ever come up with a non-confidential table that produces the same results let me know.

[KHorseman]

@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])

[dedelman_clng]

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.

[KHorseman]

@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

[bullius]

Thanks @KHorseman,

I did test these functions as well. The results were the same:

Also, this isn't normally the result you get when you try to use the DISTINCT function with blank values is it?

The interesting thing is that it gives the number of rows at the bottom, as if it has successfully created the table. It also displays the unique vales if I create a table visualisation, including the blank value.

I would just leave it, but when I try to create a relationship between this table and the vwCompanies table, it says that neither table has unique values in the RegionName column.

As I said, I have solved it for this time, but I might flag it as a bug as well (althouigh I am not totally convinced that it is a sofware issue).

[lalthan]

@bullius Try any of the below formula

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

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

[ankitpatira]

@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]

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.

[KHorseman]

@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?