cancel
Showing results for
Did you mean:
Highlighted
Helper I

Check if column contains specific based on other column grouping

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

Re: Check if column contains specific based on other column grouping

Hi @TKLAAB ,

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

If this helps, Appreciate a KUDOS!

Proud to be a Super User!

4 REPLIES 4
Highlighted
Super User IV

Re: Check if column contains specific based on other column grouping

Hi @TKLAAB ,

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

If this helps, Appreciate a KUDOS!

Proud to be a Super User!

Highlighted
Super User IX

Re: Check if column contains specific based on other column grouping

@TKLAAB , try as a new column

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

Proud to be a Super User!

Highlighted
Helper I

Re: Check if column contains specific based on other column grouping

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

Highlighted
Super User IV

Re: Check if column contains specific based on other column grouping

Hi @TKLAAB ,

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

If this helps, Appreciate a KUDOS!

Proud to be a Super User!