Create column or new table with existing column and value

[Hlerm101]

Hi BI developer,

I am BI beginer and need you help or advise.

I have a table as below. I want to keep some item from that table by specific number in column "Need Item No". 

ITEM	Display_MODE	Need Item No.
AA01	AAA111	2
BA03	AAA111	2
CC11	AAA111	2
DD12	AAA111	2
AA01	BAB101	5
BA03	BAB101	5
CC11	BAB101	5
DD12	BAB101	5
EA01	BAB101	5
ZA03	BAB101	5
MC11	BAB101	5
KD12	BAB101	5

All ITEM are the same priority but want to keep by  "Need Item No". 

For the result expected :-

1. Keep by new column by flag Y and N

ITEM	Display_MODE	Need Item No.	Flag
AA01	AAA111	2	Y
BA03	AAA111	2	Y
CC11	AAA111	2	N
DD12	AAA111	2	N
AA01	BAB101	5	Y
BA03	BAB101	5	Y
CC11	BAB101	5	Y
DD12	BAB101	5	Y
EA01	BAB101	5	Y
ZA03	BAB101	5	N
MC11	BAB101	5	N
KD12	BAB101	5	N

2. Or create a new table.

ITEM	Display_MODE	Need Item No.
AA01	AAA111	2
BA03	AAA111	2
AA01	BAB101	5
BA03	BAB101	5
CC11	BAB101	5
DD12	BAB101	5
EA01	BAB101	5

Thank you.

[v-shex-msft]

Hi @Hlerm101 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[123abc]

In Power BI, you can achieve your desired results by adding a new column to your existing table or by creating a new table. Below, I'll show you both methods:

Method 1: Adding a New Column

1. Open your Power BI report and select the table containing your data.

2. Click on the "Model" view to see your data model.

3. Select your table and click on "New Column" in the formula bar.

4. Use the following DAX formula to create a new column called "Flag":

Flag = IF([Need Item No.] = MAXX(FILTER(YourTable, [Display_MODE] = EARLIER([Display_MODE])), [Need Item No.]), "Y", "N")

Replace YourTable with the name of your table.

1. Press Enter to create the new column.

Now, you should see the "Flag" column in your table, which will display "Y" or "N" based on your criteria.

Method 2: Creating a New Table

1. In the "Model" view, right-click on the table containing your data and select "New Table."

2. Use the following DAX formula to create a new table:

NewTable =
FILTER(
YourTable,
[Need Item No.] = MAXX(FILTER(YourTable, [Display_MODE] = EARLIER([Display_MODE])), [Need Item No.])
)

Replace YourTable with the name of your existing table.

1. Press Enter to create the new table.

Now, you should have a new table called "NewTable" that contains only the rows that meet your criteria.

You can choose either method based on your preference, and it will give you the desired result in Power BI.

If I answered your question, please mark my post as solution, Appreciate your Kudos 

[Hlerm101]

Hi @123abc 

Thank you.

But, as i try the result as below.

1 New column result is Y only.

Flag = IF([Need Item No.] = MAXX(FILTER(ItemMod, [Display_MODE] = EARLIER([Display_MODE])), [Need Item No.]), "Y", "N")

2 I have try create new table but new table result is the same existing table.

NewTable =

FILTER(

ItemMod,

[Need Item No.] = MAXX(FILTER(ItemMod, [Display_MODE] = EARLIER([Display_MODE])), [Need Item No.])

)

Thank you.

[123abc · Accepted solution]

I see that you've encountered some issues with the DAX formulas provided. Let's revise the DAX formulas to create the "Flag" column and the new table correctly.

1. Creating the "Flag" Column:

   To create the "Flag" column, you can use the following DAX formula:

Flag = IF([Need Item No.] = CALCULATE(MAX(ItemMod[Need Item No.]), FILTER(ItemMod, [Display_MODE] = EARLIER([Display_MODE]))), "Y", "N")

1. Make sure that ItemMod is replaced with the actual name of your table.

   This formula uses the CALCULATE and FILTER functions to find the maximum "Need Item No" for rows with the same "Display_MODE" as the current row. If the current row's "Need Item No" matches the maximum, it sets the "Flag" to "Y," otherwise "N."

2. Creating the New Table:

   To create a new table with rows where the "Flag" is "Y," you can use the following DAX formula:

NewTable = FILTER(ItemMod, [Flag] = "Y")

1. Again, ensure that ItemMod is replaced with the actual name of your table.

   This formula simply filters the existing table (ItemMod) to include only the rows where the "Flag" is "Y."

Make sure to use these revised DAX formulas, and you should be able to create the "Flag" column and the new table with the correct results in Power BI.