Multiple rows same values in one column but not another

[mgusty33]

In the example shown below, ticket 1 was sent to multiple people for approval.  Person A Rejected it before B did anything, so the ticket was rejected, but still marked on “Open” for B. So then when I filtered “Department” as “2B” it made it look like there are

still a bunch of closed/rejected tickets still listed as “Open”. I would like to filter out the other non- “2B” departments, but I still need to see that it is rejected. Any idea on how I would do this in the query editor by adding a row somehow?

[Ashish_Mathur]

Hi,

I think my solution here will help - Filter a column of a Pivot Table on a certain condition but also show other items from that column.

[v-juanli-msft]

Hi mgusty

It should show “Rejected” when selecting other department instead of “2B” departments, is it?

I test it on my site by add an index column in query editor and create a measure.

Measure formula:

Column6 =
IF (
    CALCULATE (
        COUNT ( Table1[Status] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Ticked] ),
            Table1[Index] < SELECTEDVALUE ( Table1[Index] )
                && Table1[Status] = "Rejected"
        )
    )
        >= 1,
    "Rejected",
    "un-Rejected"
)

Best regards

Maggie

[mgusty33]

The data is technically in a query, and not a table so I am not sure if that would change the formula. However, that result is exactly what I would like showing, but is it possible to have the query show that result just in an extra column instead of measure? I would then filter the query down to just the department 2b? 

[v-juanli-msft]

Hi

Hope this will help

in Query Editor, add a conditional column as follows

or create a calculated column

Column =
IF (
    CALCULATE (
        COUNT ( Table1[Status] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Ticked] ),
            Table1[Index] < EARLIER ( Table1[Index] )
                && Table1[Status] = "Rejected"
        )
    )
        >= 1,
    "Rejected",
    "un-Rejected"
)

[mgusty33]

v-juanli-msft:

The query option still has department 2b listed as "Open". I don't want to automatically list it as "Rejected", becuase there may be times when the ticket is still listed as open for all the people, so I do want "Open" to show up. 

Ashish_Mathur:

The idea is exactly what I would like to do, but being I am pulling from a database, I am unable to add an another column to make a string. 

It may not be psosible to do. But I figured I would just ask to see if anyone had an idea. 

[mgusty33 · Accepted solution]

I managed to figure out a solution. I duplicated the query and on the new query I filtered for "Rejected" and removed duplicate ticket numbers (In case 2 of the 4 rejected). I filtered the original query on just department "2b". I then merged the new one back with original one as a new column called "New Status". It made it so some of the "New Status" columns were blank, so I then added another column with the equation 

if [#"New_Status"] = null then [Status] else [#"New_Status"])

This gave me the "Rejected" status if any person rejected it for that ticket.