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...
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.

     

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
Ashish_Mathur's avatar
Ashish_Mathur
Super User
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.