Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

SQL Case Function Convent into DAX

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

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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

 

View solution in original post

TomMartens
Super User
Super User

Hey @Anonymous ,

 

this is my sample table:

TomMartens_0-1595268817896.png

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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.

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
TomMartens
Super User
Super User

Hey @Anonymous ,

 

this is my sample table:

TomMartens_0-1595268817896.png

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
amitchandak
Super User
Super User

@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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.