topic Re: DAX Measure help in DAX Commands and Tips

DAX Measure help

Anonymous — Mon, 19 Dec 2022 11:33:37 GMT

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

Re: DAX Measure help

Anonymous — Tue, 20 Dec 2022 04:13:48 GMT

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

Re: DAX Measure help

Anonymous — Tue, 20 Dec 2022 07:08:41 GMT

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

)