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
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
107 | |
106 | |
90 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |