cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DataGuy2016
Advocate II
Advocate II

DISTINCT command pulls BLANKS

Hi there,

 

We are creating DISTINCT tables.  We are getting distinct rows, but we are also getting a BLANK row.

 

How do we remove the BLANK row so we only have non blank rows?

 

We looked at:

FILTER(DISTINCT(Transactions[CheckInLoc]), <> BLANK)

 

Thanks!

 

 

1 ACCEPTED SOLUTION

@DataGuy2016

 

Just change the column instead of all table

 

Table = DISTINCT(FILTER(VALUES(Transactions[CheckInLoc]),LEN(Transactions[CheckInLoc])>0))




Lima - Peru

View solution in original post

8 REPLIES 8
Vvelarde
Community Champion
Community Champion

@DataGuy2016

 

Hi, please try with this:

 

Table = DISTINCT(FILTER(Transactions,LEN(Transactions[CheckInLoc])>0))




Lima - Peru

Hi,

 

Nice try and thank you very much for your suggestion.

 

However...

 

I only want the CheckinLoc column with the unique values for that column.  Your query pulls all columns.  

@DataGuy2016

 

Just change the column instead of all table

 

Table = DISTINCT(FILTER(VALUES(Transactions[CheckInLoc]),LEN(Transactions[CheckInLoc])>0))




Lima - Peru

@Vvelarde , I'm having an issue with this solution. I created my table of (what appear to be) distinct values using the following DAX:

 

Units = VAR tbl1 = DISTINCT(FILTER(VALUES('Prospect Pool'[Unit]),LEN('Prospect Pool'[Unit]) > 0)) VAR tbl2 = DISTINCT('Qualification Interactions'[BUSINESS UNIT]) RETURN UNION(tbl1,tbl2)

But when I try to join that new table to another table in a one-to-many relationship, I get an error that one of the columns must contain distinct values. I took the results from the DAX above into Excel to examine more closely for issues but didn't find any. 

 

Any thoughts on what might be messing me up here? Thanks (if you're still out there!). Perhaps I need to use a different method to generate this table of distinct values from columns in multiple tables?

 

@MarkPalmberg 

 

Hi, maybe there are duplicates in the Union.

 

Also i prefer to filter the blank rows instead of Len > 0

 

DISTINCT(FILTER(VALUES(Table1[Unit]),Table1[Unit]<>BLANK()))

Regards

 

Victor




Lima - Peru

Thanks for your reply, @Vvelarde . 

 

I tried another method to generate my table of distinct values, but I'm still getting the same duplicate errors value.

 

I created two queries, one from each "Unit" column in my two source tables. I then removed dupes from each and converted them to tables, then did an append query of those two tables and removed duplicates from *that* query. No love. I could generate this list of values in a SQL query, but I'm trying to learn some DAX and M within Power BI so would like to get it working there, if for no other reason than to discover what I've done wrong.

Came in handy after may other options did not help me.

 

@Vvelarde

 

Cheers Victor!

 

Thank you very much indeed.  

 

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!