Forum Discussion

RamiDaoud's avatar
RamiDaoud
Frequent Visitor
6 years ago
Solved

Changing row values based on another coumn value

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 🙂

BirthdayIDTypeType2 (New Column)
Birth-1BasicBasic
Birth-1BasicBasic
Birth-4CreativeCreative
Birth-4BasicCreative
Birth-4BasicCreative
Birth-7BasicCreative
Birth-7CreativeCreative
Birth-7BasicCreative
Add Column
Formula
  • Pragati11's avatar
    Pragati11
    6 years ago

    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

4 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    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's avatar
      Dirk_Carpentier
      New Member
      2 years ago

      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 🙂

       

       

       

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi RamiDaoud 

    try a measure

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