Beginner question - IF statement

[Anonymous]

Hi! 

Im totally new to BI since our company has moved from Tableau recently. 

I work at customer service and I need to set up a calculation to show our Service Level %. We have two brands where we have to different targets. So basically what I need to know is: 

IF 'dwh_fact d_brand [BRAND_NAME_NEW] is "Lensway" AND IF 'dwh_fact f_css_data' [WAIT_TIME_IN_QUE] >90 THEN 0 ELSE 1
ALSO IF 'dwh_fact d_brand [BRAND_NAME_NEW] is "Lenson" AND IF 'dwh_fact f_css_data' [WAIT_TIME_IN_QUE] >120 THEN 0 ELSE 1

So basically, if Lensway has a wait time above 90 sec or if Lenson has a wait time above 120 sec, then it's 0 otherwise 1. And then an average of that in percentage. 

Hope you guys can help me! Thanks

[smpa01]

@Anonymous  welcome to Power BI !!!

I am hopin that you are atleast familiar with the basic concepts of Power BI..such as Calculated COlumn, Measures..etc.

DAX measure is a very strong concept in Power BI and the rule of thumb is not to use calculated column if you can get a measure to return what you want

You can utilize the following measure to give you what you need

Measure=AVERAGEX(CROSSJOIN(VALUES('tbl1'[BRAND_NAME_NEW]),VALUES('tbl2'[WAIT_TIME_IN_QUE])),
         SWITCH(TRUE(),'tbl1'[BRAND_NAME_NEW]="Lensway"&&'tbl2'[WAIT_TIME_IN_QUE] >90,0
		               'tbl1'[BRAND_NAME_NEW]="Lensway"&&'tbl2'[WAIT_TIME_IN_QUE] >120,0
					   ,1))

[Anonymous]

Hey guys, thanks so much for taking the time! 

I'm trying my best to get accustomed to DAX and everything but I couldn't seem to get this to work. 

I tried the functions you posted but it didn't work. One thing that I don't understand is that some of the fields won't automatically show up when typing the function. For instance, if I type IF(AND([BRAND  to get "BRAND_NAME_NEW" to show up, it doesn't. Same goes for other fields, lite date. I'm trying to make a "calls per hour" graph and have tried the "HOUR" function. But when typing HOUR(ORIG for "ORIGINATED_DATE" field (which includes date and time) nothing shows up. I'll post an image to make it clear. 

Hope this makes sense! 

[v-janeyg-msft]

Hi, @Anonymous 

Because he uses column and you use measure, measure can only get aggregated values, so it won't display column name directly.

Do you want to calculate this average at one time? Or you also needs to show 0 and 1 in other column.

As they said, 'upload some insensitive data samples and expected output' will get accurate help.

Best Regards,
Community Support Team _ Janey

[Anonymous]

Thanks for all your help. Finally got it to work with this (BRAND_KEY) is just the numeral version of the brand names: 

IF('dwh_fact f_css_data'[WAIT_TIME_IN_QUE] > 90 && 'dwh_fact f_css_data'[BRAND_KEY] = 4 ,0 , IF('dwh_fact f_css_data'[WAIT_TIME_IN_QUE] > 120 && 'dwh_fact f_css_data'[BRAND_KEY] = 14, 0, 1))

And the learning continues 🙂 

[smpa01]

@Anonymous  can you post a sample data and expected output? I could return the desired obix to you

[Anonymous]

For your specifc problem though, depending how you want to do it, you could do a calculated column with DAX like this

IF (
    AND( 'dwh_fact d_brand'[BRAND_NAME_NEW]= "Lensway" , 'dwh_fact f_css_data'[WAIT_TIME_IN_QUE] >90), 0 ,
    IF(
        AND('dwh_fact d_brand'[BRAND_NAME_NEW] = "Lenson" , 'dwh_fact f_css_data'[WAIT_TIME_IN_QUE] >120) ,0
        ,1)
)
 

Your average might look like this, but would very depending on scope. For instance, if you want to keep your average wait time within each brand your DAX will look similar to this

CALCULATE(AVERAGE([YourCalculatdColumn],ALLEXCEPT('dwh_fact d_brand'[BRAND_NAME_NEW]))

[Anonymous]

Since you are new to PowerBI I'd suggest getting familiar with some very useful websites and blogs.   There are many authors out there with great content on these basic functionalities

https://dax.guide

https://www.sqlbi.com/?s=powerbi&type=

https://docs.microsoft.com/en-us/dax/dax-function-reference

and many more.  dax.guide is really straightforward dictionary of sorts for all things DAX