Check if column contains specific based on other column grouping

[Anonymous]

Hallo

I am looking for a way to calculate the column "HasSuccess" in the table below given the two other columns "ItemID" and "ItemStatus". If an ItemID has a successful status, I want it to return True and otherwise return False.

ItemID	ItemStatus	HasSuccess
1	Success	True
1	Fail	True
1	Success	True
2	Fail	False
2	Fail	False
3	Success	True
4	Fail	True
4	Success	True
5	Fail	False

Thanks

[amitchandak]

@Anonymous , try as a new column

new column = if(countx(filter(table[ItemID] =earlier(Table[ItemID]) && Table[ItemStatus] = "Success"),table[ItemID)>0,"True","False")

[Anonymous]

@amitchandak @Pragati11 

Would it be possible to do this as a power query instead?

[Pragati11]

Hi @Anonymous ,

In Power Query, you will need to do  a grouping against your ID column. 

Check GROUP BY in Power BI in Edit Query:

https://www.poweredsolutions.co/2019/07/30/grouping-rows-with-power-bi-power-query/

Once data is grouped at ID level, you can try creating a IF-ELSE custom column.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Pragati11]

Hi @Anonymous ,

You can create a column using DAX expresion as follows:

CalcValue =

IF(COUNTROWS(FILTER(ALL(Table), (Table[ItemID] = EARLIER(Table[ItemID])) && Table[ItemStatus] = "Succes")) > 0, "True", "False")

In the above DAX replace Table with your tablename.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati