Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX Measure help

Hi!
 
I am trying to migrate a calculation from Qlik to Power BI. But I am having difficulties to make it work 100%. 
Would love to have some help here regarding this..
 
So what I want to do in words:
[No of Calls] WHERE Exclude [Wrap Up Group] = "BO Action" & "BO Support" AND Exclude [Workgroup] = *GDPR*
+
[No of Emails] WHERE Exclude [Wrap Up Group] = "BO Action" & "BO Support" AND Exclude [Workgroup] = *GDPR*
+
IF(
    Agent Type = "Live Agent",
        [No of Chats Answered LA],
           ELSE IF(
                Agent Type] = "VA Agent",
                    [No of Chats Answered VA],
                       ELSE [No of Chats Answered]
 /
[No of Orders]
 
 
I tried to create this as a DAX code without success, see below.

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]
 
I think it's the last part with the IF-statements that messes it up. Guessing maybe a SWITCH somehow will fix it?
Would love to get some help here!
 
BR Fredrik
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi,

     

    Thanks for your response!

     

    There are some things in this calculation that are not correct from what I asked about from the start.

    - Should be divided by No of Orders in the end

    - Should exclude all rows that has Workgroup = *GDPR* 

     

    I did however solve it afterwards it seems!

     

    VAR dCall =
    CALCULATE(
        [No of Calls],
            Filter( dim_wrapupgroup, NOT( dim_wrapupgroup[Wrap Up Group] IN {"BO Action",
                                                                            "BO Support",
                                                                            "STORE",
                                                                            "H&M CLUB"})),
            Filter( view_interaction, SEARCH("*GDPR*",view_interaction[Workgroup],1,0) = 0 )
    )

    VAR dEmail =
    CALCULATE(
        [No of Emails],
            Filter( dim_wrapupgroup, NOT( dim_wrapupgroup[Wrap Up Group] IN {"BO Action",
                                                                            "BO Support",
                                                                            "STORE",
                                                                            "H&M CLUB"})),
            Filter( view_interaction, SEARCH("*GDPR*",view_interaction[Workgroup],1,0) = 0 )
    )

    VAR dChat =
    SWITCH(TRUE(),
    MAX(dim_agenttype[Agent Type]) = "Live Agent", [No of Chats Answered LA],
    MAX(dim_agenttype[Agent Type]) = "VA Agent", [No of Chats Answered VA],
    [No of Chats Answered]
    )

    VAR dOrder =
    [No of Orders]

    RETURN
    IFERROR((dCall+dEmail+dChat) / dOrder,
        BLANK()
    )

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Anonymous ,

    I created some data:

     

    Here are the steps you can follow：

    1. Create measure.

    Contact per Order % =
var _sum1=
SUMX(FILTER(ALL(dim_wrapupgroup),
NOT('dim_wrapupgroup'[Wrap Up Group]) in {"BO Action", "BO Support"}
&&
CONTAINSSTRING('dim_wrapupgroup'[Workgroup],"GDPR")=TRUE())
,[No of Calls]+[No of Emails])
var _sumif=
IF(
    MAX('dim_wrapupgroup'[Agent Type]) = "Live Agent",
        MAX('dim_wrapupgroup'[No of Chats Answered LA]),
            IF(
                MAX('dim_wrapupgroup'[Agent Type]) = "VA Agent",
                    MAX('dim_wrapupgroup'[No of Chats Answered VA]),
                        MAX('dim_wrapupgroup'[No of Chats Answered])
            ))
return
_sum1+_sumif

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi,

       

      Thanks for your response!

       

      There are some things in this calculation that are not correct from what I asked about from the start.

      - Should be divided by No of Orders in the end

      - Should exclude all rows that has Workgroup = *GDPR* 

       

      I did however solve it afterwards it seems!

       

      VAR dCall =
      CALCULATE(
          [No of Calls],
              Filter( dim_wrapupgroup, NOT( dim_wrapupgroup[Wrap Up Group] IN {"BO Action",
                                                                              "BO Support",
                                                                              "STORE",
                                                                              "H&M CLUB"})),
              Filter( view_interaction, SEARCH("*GDPR*",view_interaction[Workgroup],1,0) = 0 )
      )

      VAR dEmail =
      CALCULATE(
          [No of Emails],
              Filter( dim_wrapupgroup, NOT( dim_wrapupgroup[Wrap Up Group] IN {"BO Action",
                                                                              "BO Support",
                                                                              "STORE",
                                                                              "H&M CLUB"})),
              Filter( view_interaction, SEARCH("*GDPR*",view_interaction[Workgroup],1,0) = 0 )
      )

      VAR dChat =
      SWITCH(TRUE(),
      MAX(dim_agenttype[Agent Type]) = "Live Agent", [No of Chats Answered LA],
      MAX(dim_agenttype[Agent Type]) = "VA Agent", [No of Chats Answered VA],
      [No of Chats Answered]
      )

      VAR dOrder =
      [No of Orders]

      RETURN
      IFERROR((dCall+dEmail+dChat) / dOrder,
          BLANK()
      )