DAX-Calculation best practice

[Anonymous]

Hi,

I am trying to fix a DAX-calculation were I basically want to take  No of Emails + No of Calls + No of Chats / No of Orders

However there are some filters on each metrics that need to be accounted for. For Emails and Calls I want to exclude rows with:

Wrap Up Group = "BO Action" AND "BO Support" 

and

Workgroup = *GDPR* (if there is a value with GDPR in it, it should be excluded)

For Chats we want to calculate on different metrics depending what Agent Type it is. If it's Agent Type = "Live Agent" then it should calculate No of Chats Answered LA, if it's Agent Type = "VA Agent" then it should calculate No of Chats Answered VA.

Else No of Chats Answered 

Here is a calculation I created trying to fix this accordingly:

Contact per Order % =

(CALCULATE(

    [No of Emails]+[No of Calls], Filter( dim_wrapupgroup , not( dim_wrapupgroup[Wrap Up Group] in {"BO Action", "BO Support"})),

                    Filter( view_interaction, SEARCH("*GDPR*",view_interaction[Workgroup],1,0) = 0 )

    )

+

IF(

    MAX(dim_agenttype[Agent Type]) = "Live Agent",

        [No of Chats Answered LA],

            IF(

                MAX(dim_agenttype[Agent Type]) = "VA Agent",

                    [No of Chats Answered VA],

                        [No of Chats Answered]

            )

    )

)

/

[No of Orders]

Out of cursiousity, is there any best practice here for this specifically case? For instance the Chat part? Because I have a lot of cases with these kind of calculations and would love to have some tips or suggestions about BP.

BR Fredrik

[daXtreme]

@Anonymous 

First of all, if you want fast DAX, you cannot use SEARCH. Easy as that. Instead, you should use Power Query to massage your data in such a way that looking for GDPR becomes a simple filter on a column.

Second, you should never filter a table if you can filter a column. This is the golden rule of DAX programming. Probably the most important in order to have fast and correct formulas. Instead, you should use the column(s) and KEEPFILTERS.

Also, nested IF's are better written with SWITCH. Doing something like this on a text field

MAX(dim_agenttype[Agent Type]) = "Live Agent",

is brittle (sometimes it's OK if you know for sure there's gonna be only one value in the current context) and should most likely be replaced by SELECTEDVALUE.

This is what I can tell about this code at first sight... Can't, obviously, comment on the performance.

[Anonymous]

@daXtreme 

Thanks for response!

Sometimes in the data I get the value "Infinite" so it is obviously not working as planned...

About your conclusions:

• Are you referring to creating flags that define Workgroup without GDPR? True or false, 1 or 0 so to speak. 
• Not sure what you mean. What is it exactly I am doing that is filtering the table and how are you suggestion I should do?
• Never used SWITCH, so it's a preferable solution instead of nested IF-statements? 

Sorry if this code is looking crazy, I am basically a QLIK developer that are currently trying to learn best practices for PBI atm. It is hard in terms of logic to go from on language to another, they are very different in many regards. 

BR Fredrik

[daXtreme]

There would be too much to expand upon. Grab a good book on DAX and study. THe best by far is "The Definitive Guide to DAX" by Alberto Ferrari and Marco Russo.

About your conclusions:

• Are you referring to creating flags that define Workgroup without GDPR? True or false, 1 or 0 so to speak. 
  • Yes. You should not perform free-form text searches in columns as this is slow (especially on large data sets). Instead, massage your data into the right format using Power Query.
• Not sure what you mean. What is it exactly I am doing that is filtering the table and how are you suggestion I should do?
  • This is something that you'll learn as soon as you start studying DAX properly. Read the book I mention. Basically, you should never place a full table as a filter in CALCULATE. Instead, you should only use the relevant columns with or without KEEPFILTERS depending on what you want to achieve.
• Never used SWITCH, so it's a preferable solution instead of nested IF-statements?
  • Yes, use SWITCH with many conditions as it's easier to read and maintain.

[Anonymous]

Great, I'll buy that book!

• How do I massage my data with Power Query in this case? I need to solve this in a short period of time.. Do you have any direction you can point for to more specifically?
• So KEEPFILTERS instead of FILTER is better?
• Ok, I'll try that out. 

Can you give an example if I need a flag to filter Source = Genesys as an example. Should I then create a DAX formula for this? 

BR Fredrik

[daXtreme]

@Anonymous 

Please learn to use Power Query. It can do anything that you could imagine with your data. Most things just by clicking a button. There are millions of vids on YT about Power Query...

[Anonymous]

Unfortunately this does not help me at all short term. 
I need more specific help rather then "learn Power Query".