Forum Discussion

InsightSeeker's avatar
InsightSeeker
Helper III
2 years ago
Solved

Need help with DAX formula

I need help to achieve the results shown in the table below.

 

Essentially, in the 'Category' column, if all the values are the same, then return the first value; otherwise, return 'Mix'.

 

Table

Category
Apple-Apple
Apple-Grapes
Apple
Grapes-Grapes
Grapes-Apple-Apple-Grapes

Grapes

 

Result

CategoryResult
Apple-AppleApple
Apple-GrapesMix
AppleApple
Grapes-GrapesGrapes
Grapes-Apple-Apple-GrapesMix
GrapesGrapes

 

Need Help
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    This calculated column formula works

    Column = if(SUBSTITUTE(SUBSTITUTE(Data[Category],"-",BLANK()),LEFT(Data[Category],SEARCH("-",Data[Category],,20)-1),BLANK())="",LEFT(Data[Category],SEARCH("-",Data[Category],,20)-1),"Mix")

    Hope this helps.

     

12 Replies

  • qqqqqwwwweeerrr's avatar
    qqqqqwwwweeerrr
    Solution Sage
    2 years ago

    Hi InsightSeeker 

     

    Is this what your expectation

    if yes this what i have used: 

    Measure = IF(CONTAINSSTRING(MIN('Table'[Category]),"-") = FALSE(),MIN('Table'[Category]),"Mix")

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
    Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

    Regards

      • InsightSeeker's avatar
        InsightSeeker
        Helper III
        2 years ago

        Hi qqqqqwwwweeerrr  - Your result is not correct.

         

        Below is the result i am looking for.

         

        If all values are same then return the first value for example if the values are

        Apple or

        Apple-Apple or

        Apple-Apple-Apple-Apple..... then return of Apple

         

        If the values are 

        Apple-Grapes or

        Apple-Grapes-Grapes-Apple then return of Mix

         

        Result

        CategoryResult
        Apple-AppleApple
        Apple-GrapesMix
        AppleApple
        Grapes-GrapesGrapes
        Grapes-Apple-Apple-GrapesMix
        GrapesGrapes
  • ThxAlot's avatar
    ThxAlot
    Super User
    2 years ago

    Why bother to use DAX? PQ does the trick very well.

  • AnalyticsWizard's avatar
    AnalyticsWizard
    Solution Supplier
    2 years ago

    InsightSeeker 

     

    To handle the task in Power BI where you need to check if all values in a 'Category' column are the same or mixed, you can use a DAX formula to create a calculated column. Here’s a simplified approach:

     

    Result =
VAR SplitValues = PATHITEMS(SUBSTITUTE([Category], "-", "|"), "|")
VAR UniqueValues = DISTINCT(SplitValues)
RETURN
IF(COUNTROWS(UniqueValues) = 1, MAX(SplitValues), "Mix")

     

    1. In Power BI Desktop, go to your data model and add a new calculated column.
    2. Paste the formula in the formula bar.
    3.  The new column will now categorize each entry as either the single fruit name if all components are the same, or "Mix" if they differ.

     

    This formula splits each 'Category' entry into its components, checks for uniqueness, and categorizes them accordingly. Adjust the delimiter in the `SUBSTITUTE` function if it differs.

     

    If this explanation helps, please consider marking it as the solution to help others in the community.
    Appreciate your Kudo 👍

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi InsightSeeker 

         

        As ThxAlot suggested, using Power Query to get the expected result will be easier. Otherwise if you have interest in a DAX solution, you can try below formula to add a calculated column into the current table. 

        Column = 
VAR string = SUBSTITUTE([Category], "-", "|")
VAR len = PATHLENGTH(string)
VAR splitCategories = 
    ADDCOLUMNS(
        GENERATESERIES( 1, len),
        "mylist", PATHITEM( string, [Value] ) 
    )
VAR Categories = SUMMARIZE(splitCategories,[mylist])
RETURN
IF( COUNTROWS(Categories) = 1, MAXX(Categories,[mylist]), "Mix")

        Thanks to the solution from DAX how split a string by delimiter into a list or... - Microsoft Fabric Community

         

        Best Regards,
        Jing
        If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!