DAX Measure help

[Anonymous]

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]

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 · Accepted solution]

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()

)