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.
Hi @TomMartens, @parry2k
I want to covent case statement in DAX, you suggest to use the switch. how I can find string as LIKE functions
case
when
{custbody_vm_term_type} like '%Upgrade%'
then
case
when
{class.cseg_reporting_seg2} like'%Feature%'
then
'Features'
when
{class.cseg_reporting_seg1} like '%Accessory%'
then
'Accessories'
when
{item} like '%ADD%'
OR {item} like '%AAL%'
then
'Voice Add A Lines'
when
(
{class} like '%Sales : Rate Plans : Prepaid%'
or {class} like '%Sales : Features : Prepaid%'
)
then
'Prepaid Activations'
when
{class} like '%Inventory : Equipment%'
then
'Equipment'
else
'Upgrades'
end
Else "NULL" END
if you convent into Dax It will help me to understand better.
thank you
Solved! Go to Solution.
@Anonymous , would be like this
Not the complete formula
switch( true(),
CONTAINSSTRING([custbody_vm_term_type], "Upgrade") ,
switch( true(),
CONTAINSSTRING(class[cseg_reporting_seg2], "Upgrade") ,"Features",
CONTAINSSTRING(class[cseg_reporting_seg1], "Accessory") ,"Accessories", ""
) ,""
)
use && for and
and || for OR
Hey @Anonymous ,
this is my sample table:
This is the DAX is used to create the calculated column:
calc column =
var __findthis = "green"
var __findwithin = 'Table'[some words]
return
SWITCH(
TRUE()
, FIND(__findthis , __findwithin , 1 , BLANK()) > 0 , "found the search string"
, "nothing there"
)
Make sure that you cache column references to avoid multiple evaluations of the same cell.
Regards,
Tom
HI @Anonymous ,
You can take a look at the following blog about string comparison from SQL query to DAX expressions:
From SQL to DAX: String Comparison
Regards,
Xiaxoin Sheng
Thank you all of you. @v-shex-msft @TomMartens @amitchandak @parry2k
by your support, I have successfully convent it
Type = Var _Feature = CONTAINSSTRING(report784[Reporting Segment Quantity],"Feature")
Var _Accessories = CONTAINSSTRING(report784[Reporting Segment Dollars],"Accessory")
Var _VoiceAddALines = CONTAINSSTRING(report784[item],"ADD") || CONTAINSSTRING(report784[item],"AAL")
Var _PrepaidActivations = CONTAINSSTRING( report784[Product Category], "Sales : Rate Plans : Prepaid") || CONTAINSSTRING( report784[Product Category], "Sales : Features : Prepaid")
Var _Equipment = CONTAINSSTRING( report784[Product Category], "Inventory : Equipment" )
var _BTSActs = CONTAINSSTRING(report784[Reporting Segment Quantity],"BTS Acts")
Var _Voice_Activations = CONTAINSSTRING( report784[Product Category], "Voice Activations")
Var _Upgrade_term_type = CONTAINSSTRING(report784[term type],"Upgrade")
Var _Null_term_type = CONTAINSSTRING(report784[term type],"NULL")
Var _Equipment_term_type = CONTAINSSTRING(report784[term type],"Equipment")
Var _Accessories_term_type = CONTAINSSTRING(report784[term type],"Accessories")
Var _Feature_term_type = CONTAINSSTRING(report784[term type],"Feature")
Var _Activation_term_type = CONTAINSSTRING(report784[term type],"Activation")
Return
switch( true(),
_Upgrade_term_type,
switch( true(),
_Feature ,"Features",
_Accessories, "Accessories",
_VoiceAddALines, "Voice Add A Lines",
_PrepaidActivations, "Prepaid Activations",
_Equipment, "Equipment","Upgrades"
),
_Activation_term_type,
switch( true(),
_Feature ,"Features",
_Accessories, "Accessories",
_VoiceAddALines, "Voice Add A Lines",
_PrepaidActivations, "Prepaid Activations",
_BTSActs,"BTS Activations",
_Equipment, "Equipment","Voice Activations"
),
_Equipment_term_type,"Equipment",
_Accessories_term_type,"Accessories",
_Feature_term_type,"Features",
_Null_term_type,
switch( true(),
_Feature ,"Features",
_Accessories, "Accessories",
_VoiceAddALines, "Voice Add A Lines",
_PrepaidActivations, "Prepaid Activations",
_Voice_Activations,"Voice Activations",
"Equipment"
)
,"Equipment"
)
@Anonymous glad to hear, although my recommendation would be to keep the logic in the backend in your view in sql server or a similar source, instead of adding a column using DAX.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
HI @Anonymous ,
You can take a look at the following blog about string comparison from SQL query to DAX expressions:
From SQL to DAX: String Comparison
Regards,
Xiaxoin Sheng
Hey @Anonymous ,
this is my sample table:
This is the DAX is used to create the calculated column:
calc column =
var __findthis = "green"
var __findwithin = 'Table'[some words]
return
SWITCH(
TRUE()
, FIND(__findthis , __findwithin , 1 , BLANK()) > 0 , "found the search string"
, "nothing there"
)
Make sure that you cache column references to avoid multiple evaluations of the same cell.
Regards,
Tom
@Anonymous , would be like this
Not the complete formula
switch( true(),
CONTAINSSTRING([custbody_vm_term_type], "Upgrade") ,
switch( true(),
CONTAINSSTRING(class[cseg_reporting_seg2], "Upgrade") ,"Features",
CONTAINSSTRING(class[cseg_reporting_seg1], "Accessory") ,"Accessories", ""
) ,""
)
use && for and
and || for OR
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |