Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear Community Members,
Need your help in the following:
I have a dataset:
Task ID | Timestamp | Status |
ABC | 2/2/2020 4:45 | To-Do |
ABC | 2/12/2020 23:52 | Planned |
ABC | 3/5/2020 16:18 | Planned |
ABC | 3/10/2020 14:50 | To-Do |
DEF | 1/9/2020 17:42 | Completed - Late |
DEF | 3/5/2020 12:08 | Completed |
DEF | 3/10/2020 22:26 | Overdue |
DEF | 3/14/2020 2:01 | To-Do |
CED | 1/4/2020 9:58 | To-Do |
CED | 2/10/2020 23:49 | To-Do |
CED | 2/12/2020 0:11 | Planned |
CED | 3/10/2020 1:06 | Completed |
AJC | 1/9/2020 11:56 | Planned |
AJC | 1/9/2020 17:38 | Overdue |
AJC | 2/3/2020 19:08 | Completed - Late |
AJC | 2/5/2020 11:38 | Overdue |
ADR | 1/3/2020 13:32 | To-Do |
ADR | 2/13/2020 12:10 | To-Do |
ADR | 3/6/2020 1:50 | To-Do |
ADR | 3/9/2020 1:20 | Planned |
GJK | 1/11/2020 4:10 | Completed |
GJK | 3/1/2020 1:18 | Completed - Late |
GJK | 3/5/2020 0:20 | Planned |
GJK | 3/6/2020 6:07 | Completed |
VKL | 2/15/2020 13:27 | Completed - Late |
VKL | 3/5/2020 15:03 | Overdue |
VKL | 3/11/2020 0:25 | Planned |
VKL | 3/13/2020 4:06 | Overdue |
What I need is to create a MEASURE which gives the count of status by latest time stamp:
Output required:
Status | Count |
To-Do | 2 |
Completed | 2 |
Overdue | 2 |
Planned | 1 |
There is a limitation of source data as Push data, hence cannot create any table or additional column.
@amitchandak @Greg_Deckler @Ashish_Mathur @MFelix
Cheers!
Vivek
Solved! Go to Solution.
Hi,
You may download the PBI file from this Blog article - Count tasks by status.
Hope this helps.
Hey @Anonymous
Thank you for your response.
The solution you have provided is quite intuitive but the constraint is that there cannot be any support table, nor I can use Power Query. I was running out of options hence looking for expert's advise.
Solution provided by @Ashish_Mathur worked on the sample dataset, but didn't work on complete dataset.
Please advise if there is any other alternative.
Cheers!
Vivek
https://www.vivran.in/
Connect on LinkedIn
Hi,
You may refer to my solution here.
Hope this helps.
Hello @Ashish_Mathur
Thank you for your resposne.
I tried to incorporate it in my file, but it didn't work. Please advise.
Sample file here
Cheers!
Vivek
https://www.vivran.in/
Connect on LinkedIn
Hi,
I opened a PowerBI Desktop file and and went to File > Import > Power Query, Power Pivot and Power View. Clicked on OK and then built a simple Table visual. I got the same results that you expected to see (see image below). Please cross all your formulas carefully once again.
Your solution worked perfectly when applied on the sample dataset on Power BI desktop. But when I tried replicating it on the orignal dataset, it didnot work. Hence, I shared the pbix file with orignal dataset.
You can get it from here
I appreciate your time and support.
Cheers!
Vivek
https://www.vivran.in/
Connect on LinkedIn
Hi,
You may download the PBI file from this Blog article - Count tasks by status.
Hope this helps.
You are welcome.
Hello @Ashish_Mathur ,
Need your help in this:
Using the solution you had provided, I could create the following visual:
But when I am trying to replicate it on my orignal dataset, I am getting the error:
Not sure what is causing this. Please advise.
Sample pbix file
Cheers!
Vivek
https://www.vivran.in/
Connect on LinkedIn
Hi,
It looks like for one Task ID there are multiple task owners. That is causing a problem with the result of this measure -
Hello @Ashish_Mathur ,
It had worked well on the sample dataset but when I updated the records, I am not getting the required calculation.
I checked for duplicate entries as advised by you but couldn’t find any. This is a tricky situation for me and I was hoping if you could provide more support on this:
For simplicity, I have created two tables. What I am looking for is to create a visuals like the first segment:
Regards,
Vivek
This is quite a tricky problem for DAX . If you don't need all the data loaded, one solution is to use power query to solve the problem. You can use a groupby step in power query and extract the maximum date and the value from the status column. If you download this summarised table you will get the information you need. Of course if you need the other data as well then this may be a problem
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |