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.
Hello,
I have a question about using distinct count based on a number and date in Power Bi.
I have 1 column with accountnumers which have duplicates, column accountnum, I also have dates on which
transactions have been made.
I have to create a bar chart showing active customers per year and month based on accountnumber and transaction date.
When i use distinct count I only get a total of the amount of unique accountnumber, is there any way to use distinct count combined with the date to show active clients per date?
Example:
Accountnum: Date
551 01-10-2016
551 01-11-2016
551 01-12-2016
551 01-01-2017
551 01-02-2017
551 01-03-2017
552 01-01-2015
552 01-03-2015
553 01-05-2015
554 01-01-2016
556 01-04-2017
551 should show as active in the period 10-2016 - 03-2017 in a bar chart filterable on year and month.
Hope someone can help out!
Solved! Go to Solution.
Basically it should work.
If you want to calculate the active users per date, you can just create a measure to distinct count accounts group on date level.
Active Users = CALCULATE ( DISTINCTCOUNT ( Table[AccountNum] ), ALLEXCEPT ( Table, Table[Date] ) )
Regards,
Hey,
does this not work:
Hope this helps
Hi Tom,
Thanks for the fast reply and solution, it seems to work but I still only get the total amount of 5xxx and year and month
filters don't work, should they? If so it's a relational problem, which I can solve myself.
Thanks for the help in advance!
If you want to calculate the active users per date, you can just create a measure to distinct count accounts group on date level.
Active Users = CALCULATE ( DISTINCTCOUNT ( Table[AccountNum] ), ALLEXCEPT ( Table, Table[Date] ) )
Regards,
Basically it should work.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |