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.

     

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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        InsightSeeker Can you show how those data looks like? 

        I made a test with blank and "-" in cells but the previous formula works for both. Those rows display blank in the column without any error. 

         

        I did some more test and found that the error you met is probably caused by the len argument in GENERATESERIES function. If it is blank, the error happens. So please check whether len returns correct values for all rows. You can modify the formula to return len to check the result. For example, in my below test, it should return a number on every row rather than blank.