Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All!
I am looking to create a visual that shows all the current tickets throughout time. My data source is constantly repeating data because of change of ownership and change in month. Basically, the table generates new rows every first day of the month, and I want to keep this because the owners are changing, and I want to acknowledge that. There is a similar formula I was trying to adapt, but it unfortunately doesn't take into account of the changing ownership and duplicate data (understandably). Does anyone here have an idea to how I can adapt the Current Employees formula shown in the link to take into account of duplicate data? I'm afraid I am not skilled enough to understand the DAX formula below yet.
Excel link: https://onedrive.live.com/?id=889A7EFB9759AA88%21111&cid=889A7EFB9759AA88
Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))
Ticket | Owner | Create Date | Closed Date | Date |
123 | Andrew | 6/1/2019 | 6/1/2020 | |
123 | Tom | 6/1/2019 | 7/1/2020 | |
123 | Christine | 6/1/2019 | 7/19/2020 | 8/1/2020 |
123 | Christine | 6/1/2019 | 7/19/2020 | 9/1/2020 |
234 | Lily | 7/1/2019 | 6/1/2020 | |
234 | Bob | 7/1/2019 | 7/1/2020 | |
234 | Nathan | 7/1/2019 | 8/1/2020 | |
234 | Nathan | 7/1/2019 | 8/18/2020 | 9/1/2020 |
345 | Michael | 7/1/2020 | 7/1/2020 | |
345 | Michele | 7/1/2020 | 8/1/2020 | |
345 | Tim | 7/1/2020 | 9/1/2020 | |
456 | Rebeca | 6/1/2020 | 6/1/2020 | |
456 | Rebeca | 6/1/2020 | 7/1/2020 | |
456 | Vincent | 6/1/2020 | 7/15/2020 | 8/1/2020 |
456 | Vincent | 6/1/2020 | 7/15/2020 | 9/1/2020 |
567 | Jason | 9/1/2020 | 9/1/2020 | |
678 | Cassie | 9/1/2020 | 9/1/2020 | |
789 | Patricia | 8/1/2020 | 8/1/2020 | |
789 | Patrick | 8/1/2020 | 9/1/2020 | |
890 | Kevin | 7/1/2020 | 7/1/2020 | |
890 | Tim | 7/1/2020 | 7/10/2020 | 8/1/2020 |
890 | Andrew | 7/1/2020 | 7/10/2020 | 9/1/2020 |
Solved! Go to Solution.
@Anonymous , You can actually create a new table from the above table like this
summzarize(Table, Table[Ticket],Table[Owner], "Create Date", firstnonblank(Table[Create Date],blank()),"Closed Date", lastnonblank(Table[Closed Date],blank()))
and used the blog formula
@Anonymous , You can actually create a new table from the above table like this
summzarize(Table, Table[Ticket],Table[Owner], "Create Date", firstnonblank(Table[Create Date],blank()),"Closed Date", lastnonblank(Table[Closed Date],blank()))
and used the blog formula
Hi again @amitchandak ! Thanks again for all your help!
I adapted your summary formula to my actual table but am getting null Create and Closed Dates. Can you help me out? I adapted it exactly.
Hi @Anonymous ,
Would you please explain more about your expected output? In your sample data, there are many duplicate rows like
123 | Christine | 6/1/2019 | 7/19/2020 | |
123 | Christine | 6/1/2019 | 7/19/2020 |
Do they need to be count twice?
Thanks for your understanding and support.
Best Regards,
Dedmon Dai
Hi @v-deddai1-msft! Thanks for your help! The repeating data is due to the table refreshing monthly and updating the status in the table, no matter what the table will refresh. I'd want the most recent data as long everything stays the same, but if the data stays the same other than the date of the rfresh date and the director changing.
Hi,
Based on the Data that you have shared in the original post, please show the exact result that you are expecting.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |