Find the count of rows based on conditions which has multiple status for same name

[Anonymous]

Team, I have a requirement where I need to count the rows which are Compelted and In Progress based in the below conditions.

Here is my data looks like.

I have concatenated the column Name and Item and created Name-Item

Condition 1. IF (Name-Item) is ONLY Completed then COMPLETED

Ex. see Sudhakar-Pen

Condition 2. IF (Name-Item) is BOTH Completed and also In Progress then COMPLETED

Ex. see Sudharkar-Computer

Condition 3. IF (Name-Item) is ONLY In Progress then IN PROGRESS

Ex. see Sudhakar-Chair

Can someone please help me with the same?

Thanks!

[Anonymous]

Hi @Anonymous ,

For my test ,I created a flag for different two status, and sum it based on each Name-Item. Then count rows when the sum>0.

You could use the following formula:

flag =
IF (
    SELECTEDVALUE ( 'StatusTable'[Status] ) = "Competed",
    1,
    IF ( SELECTEDVALUE ( 'StatusTable'[Status] ) = "In Progress", 0 )
)

sumFlag =
IF (
    CALCULATE (
        SUMX ( 'StatusTable', [flag] ),
        ALLEXCEPT ( 'StatusTable', 'StatusTable'[Name-Item] )
    ) > 0,
    1,
    0
)

count =
CALCULATE (
    COUNTROWS ( 'StatusTable' ),
    FILTER ( 'StatusTable', [sumFlag] = 1 )
)

My visualization looks like this:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[Fowmy]

@Anonymous 

Add this code a new column in your table:

Status Count =
IF (
    CALCULATE (
        COUNTROWS ( Table1 ),
        Table1[Status] = "Competed",
        ALLSELECTED ( Table1[Status] )
    ) > 0,
    "Competed",
    "In Progress"
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Fowmy]

@Anonymous 

You can share a sample PBIX file to check if you getting the correct results.

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn  

[Anonymous]

Hi @Fowmy , A Small change in the logic. Can you please help me with the same?

I need total count of Item with below conditions

Condition 1: If Name-Item is only Completed, I have to count those items. And

Condition 2: If Name-Item is only In Progress, I have to count of those items as well. And 

Condition 3: If Name-Item is both Completed and In Progress, then I have to count only In Progress items.

As per the above data, Total Item count is 1087 but I'm expecting 986.

Thanks in advance,

[Fowmy · Accepted solution]

@Anonymous 

Add this measure and check with your expected results.

Status Count = 
SUMX(
    'Table',
    VAR _STATUSCOUNT = 
        COUNTROWS(
            CALCULATETABLE(
                VALUES('Table'[Status]),
                ALLEXCEPT('Table','Table'[Name],'Table'[Item])
            )
        )
    RETURN 
    IF( _STATUSCOUNT=1 && ('Table'[Status] = "Competed" ||'Table'[Status] = "In Progress"),
        'Table'[Actual Item Count],
        IF(_STATUSCOUNT=2 &&  "In Progress" IN CALCULATETABLE(VALUES('Table'[Status] )),
            'Table'[Actual Item Count]
        )
    ) 
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

It works! Thanks @Fowmy 

I have an similar requirement... Can you please help me on the same?

I want to create a column called "Latest Status"

Conditions:

If Name-Item is only Completed, then "Completed" and
If Name-Item is only In Progress, then left blank and
If Name-Item is both Completed and In Progress, then only for Status Completed, then Latest Status should be "Completed" and In Progress should left blank.

Thanks in advance!