Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Everyone,
I have a column having results which comprise numbers and text. I am looking to create a new measure which shall work on the logic that if there is a number value in the existing column then just keep it as in the new measure and if there is a text "BDL" in the existing column then mark zero "0" in the new measure.
Existing Column | New Measure | Logic | |
Result | Result | ||
60 | 60 | If number, keep it as is | |
70 | 70 | If number, keep it as is | |
67 | 67 | If number, keep it as is | |
80 | 80 | If number, keep it as is | |
BDL | 0 | If BDL, then mark it as Zero | |
60 | 60 | If number, keep it as is | |
77 | 77 | If number, keep it as is | |
BDL | 0 | If BDL, then mark it as Zero | |
86 | 86 | If number, keep it as is | |
120 | 120 | If number, keep it as is | |
BDL | 0 | If BDL, then mark it as Zero | |
50 | 50 | If number, keep it as is | |
45 | 45 | If number, keep it as is |
Any help is highly appreciable.
Thanks
Samrat
Solved! Go to Solution.
Hi @Anonymous
you can try this measure
when the current row contains "BDL", CONTAINSSTRING() will return true, then test will return 0, else return current row.
test = IF(CONTAINSSTRING( MIN('Table'[Existing Column]),"BDL"),0,MIN('Table'[Existing Column]))
Regards,
OliT
Hi @Anonymous ,
Whether the advice given by @OliT has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.
Looking forward to your feedback.
Best Regards,
Henry
Hi @Anonymous
you can try this measure
when the current row contains "BDL", CONTAINSSTRING() will return true, then test will return 0, else return current row.
test = IF(CONTAINSSTRING( MIN('Table'[Existing Column]),"BDL"),0,MIN('Table'[Existing Column]))
Regards,
OliT
Hi,
In Power Query add the 'Custom Column' and write the below M query :
if Value.Type([Result]) = type number then [Result] else 0
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |