Forum Discussion

DataGuy2016's avatar
DataGuy2016
Advocate IV
9 years ago
Solved

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:...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    DataGuy2016

     

    Just change the column instead of all table

     

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

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

DataGuy2016

 

Hi, please try with this:

 

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

DataGuy2016's avatar
DataGuy2016
Advocate IV
9 years ago

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.  

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    DataGuy2016

     

    Just change the column instead of all table

     

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

    • DataGuy2016's avatar
      DataGuy2016
      Advocate IV
      9 years ago

      Vvelarde

       

      Cheers Victor!

       

      Thank you very much indeed.  

       

       

    • Emmaxson's avatar
      Emmaxson
      Advocate I
      9 years ago

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

       

    • MarkPalmberg's avatar
      MarkPalmberg
      Kudo Commander
      7 years ago

      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?

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        7 years ago

         

        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