Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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

7 REPLIES 7
smpa01
Super User
Super User

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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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! 

 

brand name.jpgwait time.jpg

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
Not applicable

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 🙂 

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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
Not applicable

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.