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.
Background to data structure:
Our users do an action which is called ‘publishing a work.’ In our database, this is reflected in a table where work_id is the primary key, and there are other columns called e.g., user_id, time_published
The table we have aggregated in our Data Warehouse (which is what is imported into Power BI) has columns (user_id, user_email, day, delivered). So if a user publishes n works on a 2017-12-19, there will be a row with [id, email, 2017-12-19, n] – but if a they did not publish anything on 2017-12-18, there is no row for that.
Problem
From this it’s easy to count how many users published a work in a time period, but I’m stuck to try and see how many users did not publish in a series of two week periods.
I have a table called All emails, with all user emails, and I made a calculated table for my relevant time periods called Thursdays
I tried something like the above to count emails where if you count how many works that email published in a time period, the result is blank – though that isn’t working for me.
did not publish in two weeks prior = calculate( distinctcount('All emails'[email]), filter('All emails', CALCULATE(distinctcount(pobbledb_works_published[email]), filter(pobbledb_works_published, and('Thursdays'[thursday_of_week] > pobbledb_works_published[delivered], pobbledb_works_published[delivered] > 'Thursdays'[thursday_of_week] - 14))) = blank() ))
Would any of you have any idea how to
Thanks very much!
Solved! Go to Solution.
You just need to add the following boolean expression to FILTER Function.
&& pobbledb_works_published[email] = 'All emails'[email]
You just need to add the following boolean expression to FILTER Function.
&& pobbledb_works_published[email] = 'All emails'[email]
Sorry for the late reply - but yes that's perfect thanks!
Abbas
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |