Measure Help: Conditional Count

[vivran22]

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

[Anonymous]

Here's what you want:

https://1drv.ms/u/s!ApyQEauTSLtOgYM2L6z98HL-5gzpXQ?e=dvcZtt

Best
D

[vivran22]

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

[Anonymous]

If you want the output in a visual (table, matrix) in PBI, it's dead easy. One sec.

Best
D

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[vivran22]

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

[Ashish_Mathur]

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.

[vivran22]

Hi @Ashish_Mathur 

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download the PBI file from this Blog article - Count tasks by status.

Hope this helps.

[vivran22]

Thanks a ton @Ashish_Mathur !

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

[Ashish_Mathur]

You are welcome.

[vivran22]

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

[Ashish_Mathur]

Hi,

It looks like for one Task ID there are multiple task owners.  That is causing a problem with the result of this measure - 

Status of last time stamp and of course the others measures which use this one as an input.

[vivran22]

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:

Sample pbix file

Regards,

Vivek

[MattAllington]

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