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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculated Column Based on Two other Column Values

I have two columns, request id and status. Requests can have multiple statuses:

Request ID                         Status

1                                              1

1                                              2

1                                              3

2                                              1

2                                              2

3                                              2

3                                              3

My problem: I have been attempting to determine how to filter a visual whose logic would display all requests and status for any request id who has a status of 3 at any point. Thus, the resulting outcome for the above data would be:

Request ID                           Status

1                                            1

1                                            2

1                                            3

3                                            2

3                                            3

A simple slicer on the status would only keep those for status 3 and not all entries for Request ID that have status 3 at some point. It seems like an incredibly basic issue but I am struggling to address it. Perhaps a calculated column that determines if 3 exists for a request id and then filter on that calculated column?

2 ACCEPTED SOLUTIONS
Solution Sage

@v-shex-msft measure would work in any table visual where the values are discrete, but if you want to have other type of visuals it's better to create a calculated column doing the test you're asking for.

The simpler I can thing would be:

```hasStatus =
VAR r = [Request ID]
RETURN
CALCULATE (
COUNTROWS ( Table ),
FILTER ( ALL ( Table ), Table[Request ID] = r && Table[Status] = 3 )
)```

And then filter for the ones that are not blank

If you want a True/False solution, it's achievable by adding an extra step

```hasStatus =
VAR r = [Request ID]
VAR t =
CALCULATE (
COUNTROWS ( Table ) + 0,
FILTER ( ALL ( Table ), Table[Request ID] = r && Table[Status] = 3 )
)
RETURN
IF ( t = 0, FALSE (), TRUE () )```

Feel free to connect with me:
LinkedIn

Frequent Visitor

I ended up solving the problem on my own, but as both of these work, I will accept the one that allows for data viz outside of table. How I solved the problem:

Query Editor -> Duplicate table -> Filter Duplicated table on the basis of status = 3 -> Merge query right outer join on new table

This may not be ideal for performance, but the data isn't terribly large, so I am not too concerned.

3 REPLIES 3
Solution Sage

@v-shex-msft measure would work in any table visual where the values are discrete, but if you want to have other type of visuals it's better to create a calculated column doing the test you're asking for.

The simpler I can thing would be:

```hasStatus =
VAR r = [Request ID]
RETURN
CALCULATE (
COUNTROWS ( Table ),
FILTER ( ALL ( Table ), Table[Request ID] = r && Table[Status] = 3 )
)```

And then filter for the ones that are not blank

If you want a True/False solution, it's achievable by adding an extra step

```hasStatus =
VAR r = [Request ID]
VAR t =
CALCULATE (
COUNTROWS ( Table ) + 0,
FILTER ( ALL ( Table ), Table[Request ID] = r && Table[Status] = 3 )
)
RETURN
IF ( t = 0, FALSE (), TRUE () )```

Feel free to connect with me:
LinkedIn

Frequent Visitor

I ended up solving the problem on my own, but as both of these work, I will accept the one that allows for data viz outside of table. How I solved the problem:

Query Editor -> Duplicate table -> Filter Duplicated table on the basis of status = 3 -> Merge query right outer join on new table

This may not be ideal for performance, but the data isn't terribly large, so I am not too concerned.

Community Support

Hi @dividebyzero,

You can write a measure to check current item to return tag if it existed specific status, then apply this measure to visual level filter to filter records.

Sample measure:

```Measure 2 =
VAR list =
CALCULATETABLE ( VALUES ( Test[T1] ), Test[T2] = 3 )
RETURN
IF ( SELECTEDVALUE ( Test[T1] ) IN list, "Y", "N" )
```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors