How to generate a list of filtered distinct rows (for a slicer)

[Ghuiles]

Hello,

I have a column from a larger table. From this column i would like to have:

- the distinct values

- excluding blanks 

- and excluding lines that have strings containing several dashes (e.g. "---", "--", "----------", etc.)

We could imagine the column as follow (ignoring the other columns):

Customer A

Customer B

---

-------

Blank

Customer A

Customer B

I am struggling with it, and until now did not find a proper way to use DISTINCT with FILTER....

Does somebody has an answer?

Cheers. G,

[Seward12533]

This DAX will create a table of unique values without blanks.  New Table is on the modeling tab. 

Unique Customers Filtered  = CALCULATETABLE(VALUES('Customer'[Customer Name]),not(ISBLANK('Customer'[Customer Name]))&&'Customer'[Customer Name]<>"---"&&'Customer'[Customer Name]<>"-------")

[Anonymous · Accepted solution]

---

@Seward12533 wrote:

This DAX will create a table of unique values without blanks.  New Table is on the modeling tab. 

Unique Customers Filtered  = CALCULATETABLE(VALUES('Customer'[Customer Name]),not(ISBLANK('Customer'[Customer Name]))&&'Customer'[Customer Name]<>"---"&&'Customer'[Customer Name]<>"-------")

---

Just add LEFT to @Seward12533 formula:

Unique Customers Filtered  = CALCULATETABLE(VALUES('Customer'[Customer Name]),not(ISBLANK('Customer'[Customer Name]))&&LEFT('Customer'[Customer Name],1)<>"-"

[Ghuiles]

Excellent! Thank you guys! 🙂

@Seward12533 Thanks!

@Anonymous Your solution works! Thanks!!! 🙂

[solvisig]

Hi Ghuiles,

Have you tried using the contains filter to remove the dash columns?



and remove empty to remove the blanks:

Just go into edit query and press the down error next to the column you would like to edit and there you will find the 'Remove Empty' function and Text filter to apply the contains dash filter.

[Ghuiles]

Whao! Thanks @solvisig and @Seward12533 for your very quick answer!

@solvisig: i know what you mean, but unfortuntely i have to do it with DAX. Sorry i did not mentioned it earlier

@Seward12533: your solution looks promising, but i would need a way to catch this "--" string flexibely, as the number of such strings may vary (from two to a lot...). Any clue on how i could do it? I tried before with the function SEARCH, but it did not work.

Best regards. G.

[Seward12533]

I'm sure there is a fancier way to get around this in DAX but a quick and dirty way would be to add a column to test to see if Customer contains "-" and then filter on that column.