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 AnalyticsWizard  - I dont see the condition PATHITEMS in my BI desktop is there any other condition i can use?

 

 

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!

  • InsightSeeker's avatar
    InsightSeeker
    Helper III
    2 years ago

    Hello Anonymous - I am getting the below error when using this DAX

     

    The arguments in GenerateSeries function cannot be blank.

     

     

    • 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
        Helper III
        2 years ago

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