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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 6
daXtreme
Solution Sage
Solution Sage

@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
Not applicable

@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

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
Not applicable

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

@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
Not applicable

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.