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
a_hauser
Frequent Visitor

How to check a string if it contains ALL other strings from a column

I try to check a string in the following table. 

 

IDcol1col2check (to be calculated)
1A&B&CB

ok

1A&B&CBok
1A&B&CAok
1A&B&CCok
2A&CAnok
2A&CAnok
2A&CAnok
2A&CAnok
3BCnok

 

One ID has always the same entry in col1. 
I have to check if col2 contains all values named in col1 for each ID. 
So for example for ID 1: A,B,C are mandatory. They are all in col2, so the check column should show "OK". 
For ID 2: A and C are mandatory but only A is in col2 -> "not OK"
ID 3: B is mandatory but C in col2 -> "not OK". 

How can I get this check column in DAX?

I hope my problem is figured out exactly enough. 

Kind regards, 
Andreas

1 ACCEPTED SOLUTION

Hi, @a_hauser 

The M language, in Power Query, is case-sensitive despite living in the same environment as DAX. DAX is case-insensitive as a formula language. 

Reference:

letter-case-sensitivity-in-dax-power-bi-and-analysis-services/ 

For your needs, you should look for a solution in PQ.

If case sensitivity is not considered, you just need to add another column:

 

Result2 = CALCULATE(MIN('Table'[Result]),ALLEXCEPT('Table','Table'[ID]) )

 

veasonfmsft_1-1669775825844.jpeg

Best Regards,
Community Support Team _ Eason

View solution in original post

4 REPLIES 4
v-easonf-msft
Community Support
Community Support

Hi, @a_hauser 

As far as I understand, when the following conditions are met at the same time, the result of column check is “ok”.
1. For each ID, the value of col2  is included in the current value of col1 in each row.

2. For each ID, the number of distinct values contained in col2 should be equal to the number of values named in col1.

Please try formula like:

Path = SUBSTITUTE([col1], "&", "||")  
Condition1 = PATHCONTAINS('Table'[Path],'Table'[col2])

 

distinctvalue in col2 = CALCULATE(DISTINCTCOUNT('Table'[col2]),FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])))
items in col1 = LEN('Table'[col1])-LEN(SUBSTITUTE('Table'[col1],"&",""))+1 
Condition2=IF('Table'[distinctvalue in col2]='Table'[items in col1],TRUE(),FALSE())

 

Result = IF('Table'[Condition1]=TRUE()&&'Table'[Condition2]=TRUE(),"ok","nok")

veasonfmsft_0-1669712818190.png

Please check attached file for more details.

 

Best Regards,
Community Support Team _ Eason

 

Hi, 
Community Support Team _ Eason

if I understand it right, your soultion gives an "ok" by counting different values. 
So when when col1 is "A&B&C" and col2 would have the values A,B,Z it would also show "ok"? 
If this  is the case, the solution is not working for me. I need a case sensitive query for comparing. 

Hi, @a_hauser 

The M language, in Power Query, is case-sensitive despite living in the same environment as DAX. DAX is case-insensitive as a formula language. 

Reference:

letter-case-sensitivity-in-dax-power-bi-and-analysis-services/ 

For your needs, you should look for a solution in PQ.

If case sensitivity is not considered, you just need to add another column:

 

Result2 = CALCULATE(MIN('Table'[Result]),ALLEXCEPT('Table','Table'[ID]) )

 

veasonfmsft_1-1669775825844.jpeg

Best Regards,
Community Support Team _ Eason

ryan_mayu
Super User
Super User

@a_hauser 

you can do this in PQ, pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.