SQL Case Function Convent into DAX

[Anonymous]

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

[amitchandak · Accepted 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

[TomMartens · Accepted solution]

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]

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"

)

[parry2k]

@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.⚡

[v-shex-msft · Accepted solution]

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

[TomMartens · Accepted solution]

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

[amitchandak · Accepted 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