Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
InsightSeeker
Helper III
Helper III

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

 

1 ACCEPTED SOLUTION

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.

Ashish_Mathur_0-1713924815198.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

12 REPLIES 12
ThxAlot
Super User
Super User

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

ThxAlot_0-1713712660718.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



AnalyticsWizard
Super User
Super User

@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 👍

Hi @AnalyticsWizard  - I dont see the condition PATHITEMS in my BI desktop is there any other condition i can use?

 

InsightSeeker_0-1713695240297.png

 

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")

vjingzhanmsft_0-1713860396810.png

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!

Hello @v-jingzhan-msft - I am getting the below error when using this DAX

 

The arguments in GenerateSeries function cannot be blank.

 

InsightSeeker_0-1713863470949.png

 

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. 

 

 

Hi @v-jingzhan-msft  - yes i do have some cells which does not have any values, how can i handle this?

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

vjingzhanmsft_0-1713922770626.png

 

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. 

vjingzhanmsft_3-1713923633297.png

 

 

 

qqqqqwwwweeerrr
Super User
Super User

Hi @InsightSeeker 

 

Is this what your expectation

qqqqqwwwweeerrr_0-1713692997099.png

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

Hi @qqqqqwwwweeerrr  - I need the result in the column instead of measure

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

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.

Ashish_Mathur_0-1713924815198.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.