Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm new to PowerBI having moved over from SAP Crystal Reports and one of the issues I'm having is replacing the custom formulas I had created previously.
We have a table that shows the type of streetlights that are used within the Borough and in Crystal I had the following formula set up:
If {LAMPS.LAMPDESC1} like "*LED*" then "LED" else
If {LAMPS.LAMPDESC1} like "*SON*" then "High Pressure Sodium - SON" else
If {LAMPS.LAMPDESC1} like "*High Pressure Sodium*" then "High Pressure Sodium - SON" else
If {LAMPS.LAMPDESC1} like "*SOX*" then "Low Pressure Sodium - SOX" else
If {LAMPS.LAMPDESC1} like "*Low Pressure Sodium*" then "Low Pressure Sodium - SOX" else
If {LAMPS.LAMPDESC1} like "*CPO*" then "Cosmopolis" else
If {LAMPS.LAMPDESC1} like "*Cosmopolis*" then "Cosmopolis" else
If {LAMPS.LAMPDESC1} like "*Electronic Ballast*" then "Electronic Ballast" else
If {LAMPS.LAMPDESC1} like "*High Pressure Mercury*" then "High Pressure Mercury" else
If {LAMPS.LAMPDESC1} like "*Low Pressure Mercury*" then "Low Pressure Mercury" else
If isnull ({LAMPS.LAMPDESC1}) then "No data" else
"Others"
Trying to set this up in PowerBI has got my completely confused. I have found help on here for if I want to search for 2 types of values using the following formula
Column =
IF (
SEARCH ( "SON*", LAMPS[LAMPDESC1],, 0 ) = 0,
IF ( SEARCH ( "LED*", LAMPS[LAMPDESC1],, 0 ) = 0, "Others", "LED Lights" ),
"SON Lights"
)
but how do I expand that out to include MULTIPLE searchs as per the previous formula I was using?
Any help would be very gratefully received!!!
Solved! Go to Solution.
This should work:
Column = SWITCH(TRUE(), SEARCH("LED", LAMPS[LAMPDESC1], 1, 0) > 0, "LED", SEARCH("SON", LAMPS[LAMPDESC1], 1, 0) > 0, "High Pressure Sodium - SON", . . . ISBLANK(LAMPS[LAMPDESC1]), "No data", "Others" )
This should work:
Column = SWITCH(TRUE(), SEARCH("LED", LAMPS[LAMPDESC1], 1, 0) > 0, "LED", SEARCH("SON", LAMPS[LAMPDESC1], 1, 0) > 0, "High Pressure Sodium - SON", . . . ISBLANK(LAMPS[LAMPDESC1]), "No data", "Others" )
Column =
SWITCH(TRUE(),
SEARCH("LED", LAMPS[LAMPDESC1], 1, 0) > 0, "LED",
SEARCH("SON", LAMPS[LAMPDESC1], 1, 0) > 0, "High Pressure Sodium - SON",
SEARCH("*High Pressure Sodium*", LAMPS[LAMPDESC1], 1, 0) > 0, "High Pressure Sodium - SON",
SEARCH("*SOX*", LAMPS[LAMPDESC1], 1, 0) > 0, "High Pressure Sodium - SON",
SEARCH("*Low Pressure Sodium*", LAMPS[LAMPDESC1], 1, 0) > 0, "Low Pressure Sodium - SOX",
SEARCH("*CPO*", LAMPS[LAMPDESC1], 1, 0) > 0, "Cosmopolis",
SEARCH("*Cosmopolis*", LAMPS[LAMPDESC1], 1, 0) > 0, "Cosmopolis",
SEARCH("*Electronic Ballast*", LAMPS[LAMPDESC1], 1, 0) > 0, "Electronic Ballast",
SEARCH("*High Pressure Mercury*", LAMPS[LAMPDESC1], 1, 0) > 0, "High Pressure Mercury",
SEARCH("*Low Pressure Mercury*", LAMPS[LAMPDESC1], 1, 0) > 0, "Low Pressure Mercury",
ISBLANK(LAMPS[LAMPDESC1]), "No data",
"Others"
)
Brilliant - this has worked perfectly! Thanks for your help.
Thanks a lot
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |