Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Let's see if I can make this message clear
I have a set of data, that contains User IDs and their purchasing date. I want to know how many distinct users were active (had transactions) during a month. This is easy as I just use DISTINCTCOUNT(), and get the desired result. The problem arises, when I want to filter out said users. I want to select a month to the count of the distinct users during that month AND how many of them were active during the whole observation period. For example , i want to select people that were making a purchase during the January of 2018, and then see how many of said people were active during other months. The problem is, that I need to do this using DAX functions, not querry or external data manipulation tools. ive tryed several different things, but can not seem to come to an answer. I am not a very experienced power BI user, but this is one of my latest attempts (though unsuccsesful):
commented lines are other attempts to this problem.
Solved! Go to Solution.
@Anonymous here is what you can do:
- create another table for slicer, which you can use folloing DAX expression
Date = VALUES( Table2[Transaction_year_month] )
- add a slicer and Transaction_Year_Month column from this new Date Table
Add following measure in your user table
Users = VAR usersinSelectedMonth = CALCULATETABLE( VALUES( Table2[User] ), Table2[Transaction_year_month] IN VALUES( 'Date'[Transaction_year_month] ) ) RETURN CALCULATE( DISTINCTCOUNT( Table2[User] ), usersinSelectedMonth )
Create a bar chart, use Transaction_Per_month and Users measure from your user table and you will have the result.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous if you can share sample data in excel with exepcted output, will get you the solution
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Transaction_year_month | User |
2011-01 | Bob |
2011-02 | Bob |
2011-03 | Bob |
2011-04 | Bob |
2011-05 | Bob |
2011-06 | Bob |
2011-07 | Bob |
2011-08 | Bob |
2011-09 | Bob |
2011-10 | Bob |
2011-11 | Bob |
2011-12 | Bob |
2011-02 | Adam |
2011-03 | Adam |
2011-04 | Adam |
2011-06 | Joe |
2011-07 | Joe |
2011-08 | Joe |
2011-01 | Victor |
2011-05 | Victor |
2011-09 | Victor |
2011-01 | Peter |
2011-02 | Peter |
2011-01 | Carl |
2011-02 | Carl |
2011-03 | Carl |
2011-04 | Carl |
2011-05 | Carl |
2011-06 | Carl |
2011-07 | Carl |
2011-08 | Carl |
2011-05 | Stewart |
2011-06 | Stewart |
2011-07 | Stewart |
2011-08 | Stewart |
2011-09 | Stewart |
2011-10 | Stewart |
2011-11 | Stewart |
2011-12 |
Thank you for your response, I can not share the actual data, but I am including some simplified mock data. The idea would be, that if I selected one of the dates, let us say 2011-05, i need to see all people that had transactions that day as well as what other months they were active. The graph should includea spike on selected month and some data points trhoughoutthe whole period. My actual data is agregated to days rather then months as well. Than kyou very much for your help!
@Anonymous is this the result you are expecting if May 2011 is selected
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Yes! looking at the data and the intended visual, this is how it should look! though not sure if it is possible to selecet more than one month using a slicer to have two line graphs for two seperate months? what exact DAX formula did you use to come to this result? and thank you very much for your help!
@Anonymous here is what you can do:
- create another table for slicer, which you can use folloing DAX expression
Date = VALUES( Table2[Transaction_year_month] )
- add a slicer and Transaction_Year_Month column from this new Date Table
Add following measure in your user table
Users = VAR usersinSelectedMonth = CALCULATETABLE( VALUES( Table2[User] ), Table2[Transaction_year_month] IN VALUES( 'Date'[Transaction_year_month] ) ) RETURN CALCULATE( DISTINCTCOUNT( Table2[User] ), usersinSelectedMonth )
Create a bar chart, use Transaction_Per_month and Users measure from your user table and you will have the result.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you very much, works like a charm!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |