Custom column to flag if multiple strings exist in other columns

[Chris2016]

Hello,

I have a scenario like the example table below and received help to create a measure that counts the distinct number of Stores that have the Type "Local Fruit" and the Subtype "Apples" and "Pears", but I also need a custom column that flags the Stores with the same specifications. More specifically, in the below example, I need a custom column that adds a 1 to each value in Store that has the Type "Local Fruit" and Subtype both "Apples" and "Pears" (i.e. "Supermarket" and "Local store", as they are the only ones who have these conditions). Any ideas on how to create this flag in a column are much appreciated! -- Thanks so much!

Stores	Type	Subtype
Supermarket	Local Fruit	Apples
Supermarket	Local Fruit	Pears
Supermarket	Local Fruit	Grapes
Supermarket	Exotic Fruit	Bananas
Supermarket	Exotic Fruit	Pineapple
Supermarket	Exotic Fruit	Coconuts
Farmers Market	Local Fruit	Apples
Farmers Market	Local Fruit	Grapes
Farmers Market	Exotic Fruit	Bananas
Farmers Market	Exotic Fruit	Pineapple
Local store	Local Fruit	Apples
Local store	Local Fruit	Pears
Local store	Local Fruit	Grapes
Local store	Exotic Fruit	Bananas
Local store	Exotic Fruit	Pineapple
Shopping Center	Local Fruit	Pears
Shopping Center	Local Fruit	Grapes
Shopping Center	Exotic Fruit	Bananas
Shopping Center	Exotic Fruit	Pineapple
Shopping Center	Exotic Fruit	Coconuts

[DataNinja777]

Hi @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,

[Chris2016]

Hi, 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 · Accepted solution]

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!