Changing row values based on another coumn value

[RamiDaoud]

Hello,

I have the following table and I need to know how i can  add new column (Type2) based on the following:

if one of the Type column values for the same BithdayID is Creative then all values in Type2 column for the same BirthdayID should be Creative otherwise Type2 should be Basic. The following table illustrates the disiered results in Type2 column.

Thank you 🙂

BirthdayID	Type	Type2 (New Column)
Birth-1	Basic	Basic
Birth-1	Basic	Basic
Birth-4	Creative	Creative
Birth-4	Basic	Creative
Birth-4	Basic	Creative
Birth-7	Basic	Creative
Birth-7	Creative	Creative
Birth-7	Basic	Creative

[Pragati11 · Accepted solution]

Hi @RamiDaoud ,

Create following column as follows:

Type2 Value = IF(COUNTROWS(FILTER(ALL(BirthData), (BirthData[BirthdayID] = EARLIER(BirthData[BirthdayID])) && BirthData[Type] = "Creative")) > 0, "Creative", "Basic")

You will get the required result as follows:

If this helps please give Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Dirk_Carpentier]

The proposed formula works if I add a new column in the default pane but if I want to create similar as custom column in the Power Query Editor than it doesn't work (error) but I have not enough knowledge to find/troubleshoot the issue...if someone maybe so kind 🙂

[kmwhite4]

Thank you! 

[az38]

Hi @RamiDaoud 

try a measure

Type2 = 
var _isCreative = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[BirthdayID]),'Table'[Type]="Creative")
RETURN 
IF(_isCreative>0,"Creative","Basic")