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.
Hi,
I'm doing a very basic count of new accounts created by date/month. So I have a account creation date column and account id (unique per customer) .
All I'm trying to do is create a table in desktop where I'm using date and for couting new customers, I have a DAX formula of distinctcount(account id) . So this should ideally give me count of new accounts created by date right? But I'm unable to get that result. My result is instead showing all dates for that month/year and then again every date it is counting all account numbers. Bascially it is not grouping by date and the result looks like this.
1st Jan | 1000 |
2nd Jan | 1000 |
3rd Jan | 1000 |
4th Jan | 1000 |
It should ideally look like
1st Jan | 100 |
2nd Jan | 210 |
3rd Jan | 300 |
4th Jan | 405 |
The numbers above are random. Can't seem to figure out what is wrong here and why it is not grouping by date.
Solved! Go to Solution.
THanks @v-yingjl @aj1973 @amitchandak . This is solved. It was related to fomatting the date columns in my case to have same data type and format across tables and had to do that in the power query view and data view.
Hi @newpi ,
Not certain what did your dataset look like. I have created a sample table like this and used distinctcount() function to get the right result:
Maybe you can share some sample table for further discussion.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@v-yingjl My data is exactly in a similar format you shared. I'm also using a Date Table to match my sign up date in the account table with date field in the date table and I'm using that date column from date table here
Attached is my measure metric and the result.
Hi @newpi ,
Could you please consider sharing a sample .pbix file for further discussion?
Best Regards,
Yingjie Li
THanks @v-yingjl @aj1973 @amitchandak . This is solved. It was related to fomatting the date columns in my case to have same data type and format across tables and had to do that in the power query view and data view.
Hi @newpi ,
Glad to hear the issue is solved. You can accept your reply as solution, that way, other community members could easily find the answer when they get same issues.
Best Regards,
Yingjie Li
Hi @newpi
Would you please share a sample of your model! that way we understand better your issue and help you.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
@newpi , with data you shared it is not clear. Make sure you have date table.
Create a rank for the dates in the account.
Create a rank column and filter for 1.
RANKX(
FILTER(
'Table',
'Table'[account ]=EARLIER('Table'[account ])
),
'Table'[Creation Date],,asc,dense
)
Or try a measure like
calculate(count(Table[Account]), values(Table[Account]),filter(All(Date), Date[Date] =Min(Date[Date])))
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
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/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
Hi @newpi
No your DAX formula is counting all the rows in the table regardless of dates. If you want to see the count by day you will need to add a slicer of the dates of your table in the desktop, and then use the slicer to filter the day where you want to see the count of IDs.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
@aj1973 My other measure count worked without the slicer but this one is not. Even after adding the slicer the result is the same.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Good luck
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |