Forum Discussion
Resolver ICustom column to flag if multiple strings exist in other columns
Hi Chris2016 ,
Thanks for clarifying that you're looking for a custom column in your main table to flag stores according to your logic.
Here's how you can achieve this directly in Power BI using DAX for a custom column:
DAX Formula for the Custom Column
You can create a custom column that checks whether each store satisfies the condition of having the type "Local Fruit" and both subtypes "Apples" and "Pears."
Flag = VAR SubtypesForStore = CALCULATE( CONCATENATEX( DISTINCT('Table'[Subtype]), 'Table'[Subtype], ", " ), FILTER('Table', 'Table'[Type] = "Local Fruit" && 'Table'[Stores] = EARLIER('Table'[Stores])) ) RETURN IF(SEARCH("Apples", SubtypesForStore, 1, 0) > 0 && SEARCH("Pears", SubtypesForStore, 1, 0) > 0, 1, 0)The formula works by defining a variable, SubtypesForStore, which collects all distinct Subtype values for a given store where the Type is "Local Fruit." This is achieved using CALCULATE and CONCATENATEX to create a comma-separated string of subtypes.
The SEARCH function is then used to check if the strings "Apples" and "Pears" exist in the SubtypesForStore string. If both are found, the condition is satisfied. Finally, the IF function evaluates the results and returns 1 if both conditions are true (i.e., the store has both "Apples" and "Pears") or 0 otherwise.
To implement this in Power BI, navigate to the Modeling tab and click on New Column. Paste the provided DAX code into the formula bar and adjust the table and column names ('Table', [Stores], [Type], [Subtype]) to match your dataset. The resulting column will dynamically flag each store, showing 1 for stores that meet the specified condition and 0 for those that do not.
This approach avoids the need for intermediate tables, directly integrating the logic into the main table and ensuring the column updates dynamically with changes in the data. Let me know if you encounter any issues! 😊
Best regards,
Super UserHi Chris2016 ,
To create a custom column that flags stores meeting the criteria of having the Type "Local Fruit" and both Subtype "Apples" and "Pears," you can use Power Query to manipulate the data.
First, load your table into Power Query. Filter the rows to include only those where the Type is "Local Fruit." Then, group the data by Stores, collecting all rows for each store into a nested table. In the grouped table, add a custom column that checks if the Subtype column within the grouped data contains both "Apples" and "Pears." If the condition is met, assign the flag value 1; otherwise, assign 0. Finally, expand the grouped table back to its original structure, adding the flag column to each row.
Here is an example of the Power Query M code to achieve this:
let
Source = YourTableName,
FilteredTable = Table.SelectRows(Source, each [Type] = "Local Fruit"),
GroupedTable = Table.Group(FilteredTable, {"Stores"}, {{"AllData", each _, type table [Stores=nullable text, Type=nullable text, Subtype=nullable text]}}),
AddFlag = Table.AddColumn(GroupedTable, "Flag", each if List.ContainsAll(List.Distinct([AllData][Subtype]), {"Apples", "Pears"}) then 1 else 0),
ExpandedTable = Table.ExpandTableColumn(AddFlag, "AllData", {"Stores", "Type", "Subtype"})
in
ExpandedTable
This code filters the rows to keep only those relevant to the condition, groups them by store, evaluates whether each store has both required subtypes, and assigns a flag accordingly. The final table includes a Flag column, where stores like "Supermarket" and "Local store" are flagged with 1 for meeting the conditions, while others are flagged with 0.
Best regards,
Chris2016Hi, thanks so much for your effort to help! I already have a custom table created for this purpose in DAX:
Restricted Table =
FILTER(CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(
'Table',
'Table'[Stores],
'Table'[Type]
),
"SubTypes",
CALCULATE(
CONCATENATEX(
VALUES('Table'[Subtype]), 'Table'[Subtype], ", "
)
)
), FILTER('Table', 'Table'[Type] = "Local fruit" && ([Subtype] = "Pears" || [Subtype]= "Apples"))
), [SubTypes] = "Pears, Apples")Or this:
Test table =
VAR newtable =
CALCULATETABLE( ADDCOLUMNS (
SUMMARIZE(
'Table',
'Table'[Stores],
'Table'[Type]
),
"@count",
CALCULATE (
COUNTROWS (
SELECTCOLUMNS (
FILTER ( 'Table', 'Table'[Type]= "Local fruit"),
"@result", 'Table'[Stores])
),
TREATAS ( { "Apples", "Pears" }, 'Table'[Subtype])
)
), FILTER('Table', [Type] = "Local fruit" && ([Subtype] = "Apples" || [Subtype] = "Pears"))
)
RETURN FILTER(newtable, [@count] =2)
What I need is an actual custom column in my main table that flags the "Stores" according to the logic.Many thanks and best regards!
- DataNinja777
Hi Chris2016 ,
Thanks for clarifying that you're looking for a custom column in your main table to flag stores according to your logic.
Here's how you can achieve this directly in Power BI using DAX for a custom column:
DAX Formula for the Custom Column
You can create a custom column that checks whether each store satisfies the condition of having the type "Local Fruit" and both subtypes "Apples" and "Pears."
Flag = VAR SubtypesForStore = CALCULATE( CONCATENATEX( DISTINCT('Table'[Subtype]), 'Table'[Subtype], ", " ), FILTER('Table', 'Table'[Type] = "Local Fruit" && 'Table'[Stores] = EARLIER('Table'[Stores])) ) RETURN IF(SEARCH("Apples", SubtypesForStore, 1, 0) > 0 && SEARCH("Pears", SubtypesForStore, 1, 0) > 0, 1, 0)The formula works by defining a variable, SubtypesForStore, which collects all distinct Subtype values for a given store where the Type is "Local Fruit." This is achieved using CALCULATE and CONCATENATEX to create a comma-separated string of subtypes.
The SEARCH function is then used to check if the strings "Apples" and "Pears" exist in the SubtypesForStore string. If both are found, the condition is satisfied. Finally, the IF function evaluates the results and returns 1 if both conditions are true (i.e., the store has both "Apples" and "Pears") or 0 otherwise.
To implement this in Power BI, navigate to the Modeling tab and click on New Column. Paste the provided DAX code into the formula bar and adjust the table and column names ('Table', [Stores], [Type], [Subtype]) to match your dataset. The resulting column will dynamically flag each store, showing 1 for stores that meet the specified condition and 0 for those that do not.
This approach avoids the need for intermediate tables, directly integrating the logic into the main table and ensuring the column updates dynamically with changes in the data. Let me know if you encounter any issues! 😊
Best regards,- Chris2016
Thanks so much, this works perfectly!
Best regards!