Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to 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
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
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]) )
Best Regards,
Community Support Team _ Eason
you can do this in PQ, pls see the attachment below
Proud to be a Super User!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
74 | |
71 | |
55 | |
37 | |
32 |
User | Count |
---|---|
71 | |
65 | |
60 | |
50 | |
45 |