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
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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors