Calculated Column Based on Two other Column Values

[dividebyzero]

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?

[Smauro · Accepted solution]

Hi @dividebyzero,

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

[Smauro · Accepted solution]

Hi @dividebyzero,

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

[dividebyzero · Accepted solution]

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. 

[v-shex-msft]

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