Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
a sollution for this problem was made in this thread - https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-formulas-for-subfiltering-a-column-based-...
Hi @Anonymous ,
Could you please share more information sbout your "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", you can share some sample data and expected result to us for analysis.
Best Regards,
Teige
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. Thank you for your help!
Hi @Anonymous ,
It's sorry that, based on my test, it looks like that we can't meet this requirement using DAX query.
Best Regards,
Teige
a sollution for this problem was made in this thread - https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-formulas-for-subfiltering-a-column-based-...
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.