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'm new to PowerBI and struggling to create code to track productivity. Below is an example of the data I am working with:
Name | ProductKey | Report Date | Note |
Jim | Silver1 | 11/14/2018 | 0 |
Jim | Silver1 | 11/15/2018 | 0 |
Jim | Silver1 | 11/16/2018 | 1 |
Jim | Silver1 | 11/17/2018 | 1 |
Jim | Silver2 | 11/15/2018 | 0 |
Jim | Silver2 | 11/16/2018 | 1 |
Jim | Gold1 | 11/14/2018 | 1 |
Jim | Gold1 | 11/15/2018 | 1 |
Jim | Gold1 | 11/16/2018 | 1 |
Jim | Gold2 | 11/15/2018 | 1 |
Jane | Bronze1 | 11/15/2018 | 1 |
Jane | Bronze1 | 11/16/2018 | 1 |
Jane | Bronze2 | 11/14/2018 | 0 |
Jane | Bronze2 | 11/15/2018 | 1 |
Jane | Bronze2 | 11/16/2018 | 1 |
Jane | Tin1 | 11/15/2018 | 0 |
Jane | Tin1 | 11/16/2018 | 1 |
Jane | Tin2 | 11/16/2018 | 0 |
Here's the situation: We are trying to calculate the number of times an individual changes the status of a line each day. When an individual completes a task, the "Note" column will change from 0 to 1. The line may be on the report for several days before and/or after the task is completed, which makes it difficult to count when the work is actually completed. For the above example, the ideal output would be the table below:
11/14/2018 | 11/15/2018 | 11/16/2018 | 11/17/2018 | |
Jim | 1 | 1 | 2 | 0 |
Jane | 0 | 2 | 1 | 0 |
Any thoughts? Thanks!
Solved! Go to Solution.
Hi @jwesle,
You can try to use following calculated column formula to replace note column, then drag this one to matrix visual value field with summary mode 'sum'.
Replaced = IF ( COUNTROWS ( FILTER ( Table, [Name] = EARLIER ( [Name] ) && [ProductKey] = EARLIER ( Table[ProductKey] ) && [Report Date] < EARLIER ( Table[Report Date] ) && [Note] = 1 ) ) > 0, 0, [Note] )+0
Regards,
Xiaoxin Sheng
Hi @jwesle,
You can try to use following calculated column formula to replace note column, then drag this one to matrix visual value field with summary mode 'sum'.
Replaced = IF ( COUNTROWS ( FILTER ( Table, [Name] = EARLIER ( [Name] ) && [ProductKey] = EARLIER ( Table[ProductKey] ) && [Report Date] < EARLIER ( Table[Report Date] ) && [Note] = 1 ) ) > 0, 0, [Note] )+0
Regards,
Xiaoxin Sheng
I would add a column and use EARLIER to return a zero or 1 for whether an item was completed on a particular row. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |