Forum Discussion

Chris2016's avatar
Chris2016
Icon for Resolver I rankResolver I
1 year ago
Solved

Custom column to flag if multiple strings exist in other columns

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 ...
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    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's avatar
Chris2016
Icon for Resolver I rankResolver I
1 year ago

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's avatar
DataNinja777
Icon for Super User rankSuper User
1 year ago

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's avatar
    Chris2016
    Icon for Resolver I rankResolver I
    1 year ago

    Thanks so much, this works perfectly!

    Best regards!