Skip to main content
cancel
Showing results for 
Search instead 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

Reply
guptasunal
Helper I
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
MarkS
Resolver IV
Resolver IV

Hi @guptasunal

 

As a calculated column try this:

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

View solution in original post

3 REPLIES 3
MarkS
Resolver IV
Resolver IV

Hi @guptasunal

 

As a calculated column try this:

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

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors