Forum Discussion

rocky09's avatar
rocky09
Solution Sage
8 years ago
Solved

Issue with Summarizing Status Count

See below table, I have cat. column, where Users start working on each cat. and change the status to Inprogress, Complete and some times rework.   So, if a Cat. worked on 5 days and status will be ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi rocky09,

     

    Based on my test, the formula below should work in your scenario.

    Count = 
VAR maxDate =
    CALCULATE (
        MAX ( Data[Date] ),
        FILTER ( ALL ( Data ), Data[Cat.] = EARLIER ( Data[Cat.] ) )
    )
VAR minDate =
    CALCULATE (
        MAX ( Data[Date] ),
        FILTER ( ALL ( Data ), Data[Cat.] = EARLIER ( Data[Cat.] ) )
    )
RETURN
    IF (
        Data[Status] = "Completed",
        1,
        IF ( Data[Date] = maxDate, 1 + DATEDIFF ( minDate, maxDate, DAY ) / 10, 0 )
    )

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

rocky09's avatar
rocky09
Solution Sage
8 years ago

I have older version of excel. So, I cannot see the measure you used in the excel. That is why i have asked you to provide pbi file.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Surprisingly, when i import the Excel workbook contents into PowerBI desktop, i get a message saying that a table of multiple values was supplied where a single value was expected.  I get the perfect solution in Excel though.  Here is the calculated field formula i wrote

 

=IFERROR(COUNTROWS(FILTER(VALUES(Data[Cat.]),CALCULATE(LOOKUPVALUE(Data[Status],Data[Date],MAX(Data[Date])),ALL(Data[Status]))=VALUES(Data[Status]))),BLANK())
  • rocky09's avatar
    rocky09
    Solution Sage
    8 years ago

    Hi Thank you,

     

    Now, i am getting error "A table of multiple values was supplied where a single value was expected."

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    That is exactly what i mentioned in my previous post.  I get the same error.  It works fine in Excel though.  Looks like a bug in PowerBI desktop.

  • rocky09's avatar
    rocky09
    Solution Sage
    8 years ago

    oh.. will wait for someone to confirm if this is the actually bug with Power Bi Desktop as it is working fine in Excel. Weired.