Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
How can I achieve this in Power BI?
Thank you in advance!
Solved! Go to 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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |