Forum Discussion
Count Distinct Values By Column and by Date
Hi all
I have a table similar to the following
The table is already link to a DateTable
DBName is the name of the database providing the data
CustomerID is the ID of each customer in that database.
As there are multiple databases, the customer ID can be repeated. Therefore, if an ID is listed in 2 different databases, it will actually identify 2 different customers.
I need to calculate the number of unique customers by year.
Hope I am clear, else I can provide more details, thank you.
Hi,
Please find the below DAX measure solution.
DistinctCountOfCustomer = CALCULATE(DISTINCTCOUNT(DB_Table[CustomerID]),VALUES(Date_Table[Year]))
It is working fine. Please check and advise if it also works as per you.
Thanks,
Rakesh
If you need across the used combined column. If you need by DB and By year. Then you can take distinct of customer ID. But if you are using DBname in your visual then the combined one will also work.
6 Replies
Not sure I got it completely.
There can be a few ways. But I will create a combined Column and then take distinct count
New column = Table[DBName]& "-"&Table[CustomerID]
Count Customer = calculate(distinctcount(Table[New column]))
amitchandak that is simple and should work, thank you.
HOwever, how do I count unique customers by Year?
You can use year filter or datesytd or totalytd. make sure you have date table
example.
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Hi,
Please find the below DAX measure solution.
DistinctCountOfCustomer = CALCULATE(DISTINCTCOUNT(DB_Table[CustomerID]),VALUES(Date_Table[Year]))
It is working fine. Please check and advise if it also works as per you.
Thanks,
Rakesh
If you need across the used combined column. If you need by DB and By year. Then you can take distinct of customer ID. But if you are using DBname in your visual then the combined one will also work.
