Forum Discussion

vivran22's avatar
vivran22
Community Champion
6 years ago
Solved

Measure Help: Conditional Count

Dear Community Members,

 

Need your help in the following:

 

I have a dataset:

 

Task IDTimestampStatus
ABC2/2/2020 4:45To-Do
ABC2/12/2020 23:52Planned
ABC3/5/2020 16:18Planned
ABC3/10/2020 14:50To-Do
DEF1/9/2020 17:42Completed - Late
DEF3/5/2020 12:08Completed
DEF3/10/2020 22:26Overdue
DEF3/14/2020 2:01To-Do
CED1/4/2020 9:58To-Do
CED2/10/2020 23:49To-Do
CED2/12/2020 0:11Planned
CED3/10/2020 1:06Completed
AJC1/9/2020 11:56Planned
AJC1/9/2020 17:38Overdue
AJC2/3/2020 19:08Completed - Late
AJC2/5/2020 11:38Overdue
ADR1/3/2020 13:32To-Do
ADR2/13/2020 12:10To-Do
ADR3/6/2020 1:50To-Do
ADR3/9/2020 1:20Planned
GJK1/11/2020 4:10Completed
GJK3/1/2020 1:18Completed - Late
GJK3/5/2020 0:20Planned
GJK3/6/2020 6:07Completed
VKL2/15/2020 13:27Completed - Late
VKL3/5/2020 15:03Overdue
VKL3/11/2020 0:25Planned
VKL3/13/2020 4:06Overdue

 

What I need is to create a MEASURE which gives the count of status by latest time stamp:

 

 

Output required:

 

StatusCount
To-Do2
Completed2
Overdue2
Planned1

 

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

DAX
Need Help

14 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    6 years ago

    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

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        6 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    If you want the output in a visual (table, matrix) in PBI, it's dead easy. One sec.

    Best
    D
    • vivran22's avatar
      vivran22
      Community Champion
      6 years ago

      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