cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Match all rows within a single column and tag as PreExist/New for all Product Categories individual

Product Category Text String Ideal Result
1                            XX             PreExist
1                            XX             PreExist
1                            XX             PreExist
1                            XX             PreExist
2                            XX             New
2                            XY             New
2                            XZ             New
2                            XA             New
3                            XX             PreExist
3                            XX             PreExist
3                            XY             New
3                            XZ             New

The problem is to calculate column "Ideal Result" above from the first two columns within the table. For Product Category 1 if the text string existed before it should say "PreExist" else "New". All rows within a product category needs to match all rows within that category because even if the value exists after, it should still say "PreExist"

I am a beginner in DAX and took a shot at writing something:

Ideal Result = IF(CALCULATE(DISTINCTCOUNT(TableName[Text String])<4,FILTER(TableName,TableName[Product Category]=EARLIER(TableName[Product Category])))=TRUE(),"PreExist","New")

But need a better way to iterate within the column to compare every cell with every cell within a category.

1 ACCEPTED SOLUTION
Resolver IV

As a calculated column try this:

`Ideal Result = IF( CALCULATE(COUNT(Table1[Text String]))=1,"New","PreExist")`
3 REPLIES 3
Resolver IV

As a calculated column try this:

`Ideal Result = IF( CALCULATE(COUNT(Table1[Text String]))=1,"New","PreExist")`
Helper I

Awesome! Thanks @MarkS

As i said, i am new to DAX, how did CALCULATE(COUNT()) count only the no of reoccurence of unique string within a category while COUNT() counts all rows. The result is exactly what i needed, but couldn't understand how CALCULATE(COUNT()) helped here.

Do you any recommendations for any article/book/url to start writing and learning these DAX calculations? Like the one above.

Resolver IV

Hi @guptasunal,

It has to do with row context and the fact that this is a calculated column and not a measure.  I probably could not explain it correctly but there are posts about it.

www.sqlbi.com has good indepth articles about DAX formulas and functions.