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

[a_hauser]

I try to check a string in the following table. 

ID	col1	col2	check (to be calculated)
1	A&B&C	B	ok
1	A&B&C	B	ok
1	A&B&C	A	ok
1	A&B&C	C	ok
2	A&C	A	nok
2	A&C	A	nok
2	A&C	A	nok
2	A&C	A	nok
3	B	C	nok

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

[v-easonf-msft]

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")

Please check attached file for more details.

Best Regards,
Community Support Team _ Eason

[a_hauser]

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. 

[v-easonf-msft · 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]) )

Best Regards,
Community Support Team _ Eason

[ryan_mayu]

@a_hauser 

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