Forum Discussion
Create column or new table with existing column and value
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.
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")
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."
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")
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.
Community ChampionIn 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
Open your Power BI report and select the table containing your data.
Click on the "Model" view to see your data model.
Select your table and click on "New Column" in the formula bar.
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.
- 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
In the "Model" view, right-click on the table containing your data and select "New Table."
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.
- 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
Hi 123abc
Thank you.
But, as i try the result as below.
1 New column result is Y only.
2 I have try create new table but new table result is the same existing table.
Thank you.
- 123abc
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.
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")
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."
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")
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.