Forum Discussion

InsightSeeker's avatar
InsightSeeker
Icon for Helper III rankHelper 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.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

I have attached a sample pbix. Hope it would be helpful. 

 

Do you have any rows that may have blank or empty values in the Category column? This may cause this error. 

 

 

InsightSeeker's avatar
InsightSeeker
Icon for Helper III rankHelper III
2 years ago

Hi Anonymous  - yes i do have some cells which does not have any values, how can i handle this?