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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
freginier
Solution Specialist
Solution Specialist

Dax text comparaison

Hi all I need your help !

 

I have a table like below, I need to comparaire value from category A with values from category A.1 and A.2

I need a new column with flag "same" or "different".

 

How can I do this ? 

 

CategoryValue
AABC
A.1A
A.2ABC
A.2DEF
A.3ABC
A.4DEF
1 ACCEPTED SOLUTION
Royel
Helper II
Helper II

Load your data into the power bi desktop

Royel_0-1658993536818.png

Now create the flowing calculated column 
Status =
VAR val = data[Value]
VAR result =
CALCULATE ( COUNTROWS ( data ), ALL ( data ), data[Value] = val )
RETURN
IF ( result == 1, "different", "Same" )

You will get your expected results. 

Royel_1-1658993664859.png

 

View solution in original post

13 REPLIES 13
Royel
Helper II
Helper II

Load your data into the power bi desktop

Royel_0-1658993536818.png

Now create the flowing calculated column 
Status =
VAR val = data[Value]
VAR result =
CALCULATE ( COUNTROWS ( data ), ALL ( data ), data[Value] = val )
RETURN
IF ( result == 1, "different", "Same" )

You will get your expected results. 

Royel_1-1658993664859.png

 

freginier
Solution Specialist
Solution Specialist

@Royel ,Thank you but I need to add a condition on Category because I can have a completly different categories like B etc. How can I do that ? Thank you !

 

Why do we not use EARLIER function ? 

@freginier see what I wrote please

need to comparaire result of A category to all sub categories. But I can have more than one primary category

@freginier my friend, you are making me take gueses here..
Do you want to do a quick zoom to show me?

it's very simple 😉

 

See the table below I need to find when :

- Category A value (ABC) it's repeat on subcategories (A.2 and A.3) then flag "SAME"

- Category B value (DE) it's repeat on subcategories (B.2) then falg "SAME" 

CategoryValue
AABC
A.1A
A.2ABC
A.2DEF
A.3ABC
A.4DEF
BDE
B.1EF
B.2DE

 

more clear ? 

@freginier nope 🙂 becaue I guess your data doesn't look like A and B so I need to actually see what's going on 🙂
If you want to do a zoom I'm available to help

I can't show you more than A and B because it's confidential data ! 

The @Royel answer work just need to know how to add a condition on category because I have more than one primary category

@freginier  ok ok haha , let's try this (won't work, but it will derive from what you see here. Just want to clarify my point, look at my next reply):

 

 

Status =
VAR val = data[Value]
VAR cat = data[Cateogry]
VAR result =
CALCULATE ( COUNTROWS ( data ), ALL ( data ), data[Value] = val, data[Category] = cat )
RETURN
IF ( result == 1, "different", "Same" )

 

 

Why don't use EARLIER function ? 

@freginier EARLIER fucntion was used greatly and was very important until the introduction of Variables. That new capability made the EARLIER function not needed becasue the VARs are more clear to read. We can do it also with EARLIER but it's best to start working with VARs cause they have a lot of benefits

@freginier  that wouldn't work exactly for the reason I said. I need to know how to filter by the same cateogty rows and for that I need to know what is the textual logic. If they all start with the same letter and that is different between the categories then ok, we can do that, but that is exactly why I told you that I need to see the data or that you need to give more info. 

SpartaBI
Community Champion
Community Champion

@freginier hey, can you just clarify when do you want to get "Same" and when "Different"? on your sample data.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.