Help Needed to Convert Vlookup Formula to PowerBi

[Anonymous]

Hi All,

Hope you guys are doing well.

I need help on the below data and formulas how to convert them into powerbi.

1. =VLOOKUP(DATEDIF(B2,M2,"d"),SLA,2,1) :- how do I perform this in Powerbi 

So below is the scenario

So I have a tab which captures all the incidents. I have added a row reference and the formula to capture SLA where the above formulation is done. The SLA is captured from a different Tab named configuration. I want to perform this analysis in PowerBi . Thanks in advance for your help. Below are the screenshots for the same.

Looking forward to your help on the same at the earliest.

Regards

[Fowmy · Accepted solution]

@Anonymous 

Add this as a column to your table:

SLA = 

VAR _DAYS = DATEDIFF(Table1[OPEN],Table1[CLOSE],DAY)

RETURN
SWITCH( TRUE() , 
    _DAYS < 1 , "< 1 Day",
    _DAYS >= 1  && _DAYS <= 5  , "1 to 5 Days",
    _DAYS >= 6  && _DAYS <= 10  , "6 to 10 Days",
    _DAYS >= 11  && _DAYS <= 20  , "11 to 20 Days",
    "> 20 Days"
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous · Accepted solution]

@Fowmy  Thanks a ton Fowmy, that really helped.

Have couple of more queries around the same data.

Will get back to you on that.

Thanks again.

[Fowmy · Accepted solution]

@Anonymous 

Add this as a column to your table:

SLA = 

VAR _DAYS = DATEDIFF(Table1[OPEN],Table1[CLOSE],DAY)

RETURN
SWITCH( TRUE() , 
    _DAYS < 1 , "< 1 Day",
    _DAYS >= 1  && _DAYS <= 5  , "1 to 5 Days",
    _DAYS >= 6  && _DAYS <= 10  , "6 to 10 Days",
    _DAYS >= 11  && _DAYS <= 20  , "11 to 20 Days",
    "> 20 Days"
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous · Accepted solution]

@Fowmy  Thanks a ton Fowmy, that really helped.

Have couple of more queries around the same data.

Will get back to you on that.

Thanks again.

[Anonymous]

Hi Everyone,

To build onto this, below are some of the issues or concerns I am facing in moving the Excel sheets I work on into PowerBi

1. I have a Task Sheet where in all the various activities of engineers are captured. But I do here is go through all the individual short description based on my understanding categorize them in the Category column as shown in the screenshot and then create a Chart based on that in Pivot.

How do I perform this in PowerBI?

[Fowmy · Accepted solution]

@Anonymous 

To have the same analysis done in Power BI, Insert a Column Chart, drag and drop the category in the AXIS, NUMBER in the Value.
then drag and drop the STATE and TASK TYPE on to two slicers on the canvas. now you can filter by the slicers for any value.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Hi,

Below is the analysis I was working on in excel. Trying to replicate the same on PowerBi.

Top 10 Incidents Count vs Avg Completion Days

1. The below screenshot is the Incident source data from where the data is fetched of doing the pivot.

2. Below is the calculation and analysis conducted to get the desired chart 

So the Ticket Count is basically the same as Total Column count

Avg Days = This is derived by using the formula H5/F5

Completion Days Formula = =SUMIFS(Incedent_CloseDate!$O:$O,Incedent_CloseDate!$J:$J,'Incidents Closed Final'!E5)

How can I replicate the same in PowerBi to produce the same chart.

[Anonymous]

Also the further Analysis which Im looking at is the below dashboard

Overall Analysis

In the above chart the Task, Change Request, Security Task and Problem are all part of the same sheet hence putting them in chart was easier in PowerBi. But for Incidents the sheet is different so I manually take the count and feed it in the table and create a chart out of it. 

As of now the chart in PowerBi has all except Incident. How do I bring the total count in this chart if the Incident Sheet is different.