topic Re: Measure Help: Conditional Count in DAX Commands and Tips

Measure Help: Conditional Count

vivran22 — Fri, 20 Mar 2020 06:39:07 GMT

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

Re: Measure Help: Conditional Count

MattAllington — Fri, 20 Mar 2020 07:44:44 GMT

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

Re: Measure Help: Conditional Count

Ashish_Mathur — Fri, 20 Mar 2020 11:52:24 GMT

Hi,

You may refer to my solution here.

Hope this helps.

Re: Measure Help: Conditional Count

vivran22 — Fri, 20 Mar 2020 13:32:30 GMT

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

Re: Measure Help: Conditional Count

Anonymous — Fri, 20 Mar 2020 22:58:22 GMT

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

Best
D

Re: Measure Help: Conditional Count

Anonymous — Fri, 20 Mar 2020 23:31:53 GMT

Here's what you want:

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

Best
D

Re: Measure Help: Conditional Count

Ashish_Mathur — Sat, 21 Mar 2020 05:10:15 GMT

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.

Re: Measure Help: Conditional Count

vivran22 — Sat, 21 Mar 2020 05:13:43 GMT

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

Re: Measure Help: Conditional Count

vivran22 — Sat, 21 Mar 2020 05:19:46 GMT

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

Re: Measure Help: Conditional Count

Ashish_Mathur — Sat, 21 Mar 2020 06:05:15 GMT

Hi,

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

Hope this helps.

Re: Measure Help: Conditional Count

vivran22 — Sat, 21 Mar 2020 08:11:25 GMT

Thanks a ton Ashish_Mathur !

Cheers!
Vivek

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

Re: Measure Help: Conditional Count

Ashish_Mathur — Sat, 21 Mar 2020 08:52:49 GMT

You are welcome.

Re: Measure Help: Conditional Count

vivran22 — Mon, 23 Mar 2020 12:31:56 GMT

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

Re: Measure Help: Conditional Count

Ashish_Mathur — Mon, 23 Mar 2020 12:46:31 GMT

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.

Re: Measure Help: Conditional Count

vivran22 — Thu, 02 Apr 2020 06:19:58 GMT

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