DAX Measure - Number of distinct values from delimited string

[MarioTinton]

I have a table like the following:

Date	Visited Customers
2017-AUG-01	Bobby,John,Richard,Felicia
2017-AUG-02	Albert,John

I want to have a measure that calculates number of unique customers within a date range. So from example data above:

• If date slicer is set only to August 1st, it should display 4
• If date slicer is set between August 1 and August 2, it should display 5 (John was visited twice but counted as one unique customer)

How can I achieve this in Power BI?

Thank you in advance!

[kaushikd]

@MarioTinton

You have to Model your data into this form.

To achieve this follow the steps;-

1. R.click on the Table and select Edit Query 

2. R.Click on VisitedCustomer Field and click on Split Column-->By Delimeter-->Comma-->At each Occurance.

3. R.Click on Date field and click on Unpivot Other Columns.(It will auto generate 2 column attribute and value)

4. Delete the Attribute Column.Rename the value column to VisitedCustomer.

5. Click on DownArrow

  of the VisitedCustomerField and Uncheck the Blank field.

Now the Modelling is Done.

Create a report using it Report 

Please mark this as a solution if this is what you required.

[tringuyenminh92 · Accepted solution]

Hi @MarioTinton,

Go to Query Editor -> In Home tab -> Split Column -> by delimeter -> Advanced options -> Split into (Rows).

Now drag the "Vistied Customers" fields to card/chart, right click in value field and choose Count (Distinct) option. 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen