cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 🙂

 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
1 ACCEPTED SOLUTION
Super User

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

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

4 REPLIES 4
Super User

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

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

New Member

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 🙂

New Member

Thank you!

Community Champion

try a measure

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

do not hesitate to give a kudo to useful posts and mark solutions as solution

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors