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 all,
back to DAX after a while and I am a bit rusty. Can someone help me building a formula?
I have a calendar table I use as a slicer.
I have a table with the user data and with one record for each visit in the "Visit Date" column.
I would like to create a measure, so, when for example someone chooses "February 2022" in the slicer, it returns the number of active users for that time period (I will have other slicers like account name and so on.
An active user is considered who had at least one visit date for that given calendar month.
Would be possible to get help with this formula?
Thanks a lot!
Solved! Go to Solution.
No specific reason. I assumed there would be a 1:1 mapping between account ID and email based on the sample you provided. Using the email column should give you the same result.
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊
Hi @Anonymous
Ensure that you have a relationship defined between your data table and the date table.
Once that is done, create a measure like below :
Please note that whatever field you use as the slicer for month must be used in the highlighted part in the measure. I have used the monthyear field in my example.
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊
Any reason why you use UserEmail[Account ID] instead of UserEmail[Email]?
No specific reason. I assumed there would be a 1:1 mapping between account ID and email based on the sample you provided. Using the email column should give you the same result.
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊
@rohit_singh unfortunately I can't share the original data because it is confidential but it looks something like this:
Visit Date | Account ID | |
emaila@test.com | 1/1/2022 | ABCD |
emaila@test.com | 12/1/2022 | ABCD |
emaila@test.com | 26/2/2022 | ABCD |
emailb@test2.com | 15/1/2022 | EFGH |
emailb@test2.com | 18/3/2022 | EFGH |
And so on...So in the example above in Jan I have 2 active users and in Feb and March 1 per month.
And I have a connection between the Visit Date and a Date Helper table like this:
Hi @Anonymous ,
Please provide sample data in text format (not a screenshot) and the expected output.
Kind regards,
Rohit
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |