Print Status based on multiple conditions

[Anonymous]

Team, I have a table like below and I want to create a new column Latest Status with below mentioned conditions

Here are the conditions

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!

[Fowmy · Accepted solution]

@Anonymous 

Please add the following Measure:

Status New = 
    VAR _STATUS = SELECTEDVALUE('Table'[Status])
    VAR _STATUSCOUNT = 
        COUNTROWS(
            CALCULATETABLE(
                VALUES('Table'[Status]),
                ALLEXCEPT('Table','Table'[Name],'Table'[Item])
            )
        )
    RETURN 
    IF( _STATUSCOUNT=1, IF(_STATUS = "Competed", "Competed", BLANK() ) ,  
            IF( "Competed" IN CALCULATETABLE(VALUES('Table'[Status] )), "Competed", BLANK()))

________________________

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]

@Fowmy Thanks for your response. Small change in the logic.

Condition 1: If Nov-Item is only Completed, then it is Completed and 

Condition 2: If Nov-Item is both Completed and In Progress, we should consider the the common items as completed and unique items as In Progress. 

Ex: Sudhakar-Computer has both In Progress (50) and Completed (40). My Problem is, Out of these 50 In progress items, Completed 40 are also included. Which means 40 are common in both the status and only 10 are new/unique. In Latest Status, I want to print the common 40 as Completed and new/unique 10 are In Progress.

When I put this in Pie chart it should show me 2 categories (Completed and In Progress) Completed should show 40 and In progress should show 10 instead of 50. When I drill through to details page, I should see all 40 Completed Items and 10 In progress items.

Can you please help me with the same?

Thanks in advance!

[amitchandak]

@Anonymous , for last status you need an index column

then you can try a column like this

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

new column =
var _comp = countx(filter(table, [Name] = earlier([Name]) && [Item] = earlier([Item]) && [Status] = "Completed"),[Status])+0
var _inprogress = countx(filter(table, [Name] = earlier([Name]) && [Item] = earlier([Item]) && [Status] = "Completed"),[Status])+0
var _comp = maxx(filter(table, [Name] = earlier([Name]) && [Item] = earlier([Item])),lastnonblankvalue([idex],max(Table[Status])))+0

return
Switch( True() ,
_comp > 0 && _inprogress =0 , "Completed",
_comp > 0 && _inprogress >0 && _comp = "Completed", "Completed",
blank()
)