Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DAX-Calculation best practice

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

6 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        daXtreme
        Icon for Solution Sage rankSolution Sage
        3 years ago

        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.