Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Beginner question - IF statement

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

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

     

     

     

     

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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! 

       

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

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