cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## A calculated Column where fields are defined by a number in a different field

Hi all!

I'm new to the forums so I'm not sure if this kind of task has been adressed before.

I'm trying to create a column where the fields are given a status' depening on how many days it took for a ticket to be resolved (Each ticket corresponds to one row).
In PBI desktop I have created a calculated column based of two date columns (Submitted and resolved) which produces a number.
I want to have a new column where the field is defined by that number.
I'm hoping to end up with a table with a table that looks something like this:

 Status 2016 2017 2018 Same Day 1 4 2 1  to 4 3 5 12 5  to 10 5 6 5 More Than 11 6 5 3

(I'm aiming to use the matrix visual}

my formular looks like this and im not sure how far off the mark I am with the syntax

column =
(
IF([IvantiToCompletionDaysColumn]= (1<) , "Same Day", 0)
IF([IvantiToCompletionDaysColumn] = (1 <=> 4) ,"1 to 4", 0)
IF([IvantiToCompletionDaysColumn] = (5 <=> 10) ,"5 to 10", 0)
IF([IvantiToCompletionDaysColumn] > 11 ,"11+", 0)
)

Any help with this would be mightly appreciated

Cheers

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

Try this:

```Category =
SWITCH(
TRUE(),
[Days]<1, "Same Day",
[Days]>=1 && [Days]<=4,"1 to 4",
[Days]>=5 && [Days]<=10,"5 to 10",
[Days]>=11,"11+"
)```

[Days] would be your column of how many days open.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

2 REPLIES 2
Highlighted
Super User IV

Try this:

```Category =
SWITCH(
TRUE(),
[Days]<1, "Same Day",
[Days]>=1 && [Days]<=4,"1 to 4",
[Days]>=5 && [Days]<=10,"5 to 10",
[Days]>=11,"11+"
)```

[Days] would be your column of how many days open.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Frequent Visitor

Worked like magic!

thank you!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors