cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

 

Status201620172018
Same Day142
1  to 43512
5  to 10565
More Than 11653

 

(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
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.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Highlighted
Super User IV
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.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted

Worked like magic!

thank you!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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