cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

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

Accepted Solutions
Super User IV
Super User IV

@M_OVAISJANZEB , 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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Super User II
Super User II

Hey @M_OVAISJANZEB ,

 

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

Community Support
Community Support

HI @M_OVAISJANZEB ,

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
Super User IV
Super User IV

@M_OVAISJANZEB , 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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Super User II
Super User II

Hey @M_OVAISJANZEB ,

 

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

Community Support
Community Support

HI @M_OVAISJANZEB ,

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

Helper IV
Helper IV

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"

)

 

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






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.





Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors