Forum Discussion

mgusty33's avatar
mgusty33
Frequent Visitor
8 years ago
Solved

Multiple rows same values in one column but not another

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?

 

Need Help
Show and Tell
  • mgusty33's avatar
    mgusty33
    8 years ago

    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.

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    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's avatar
      mgusty33
      Frequent Visitor
      8 years ago

      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's avatar
        v-juanli-msft
        Community Support
        8 years ago

        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"
)