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

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity 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
    Icon for Community Support rankCommunity 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"
)

     

     

    • mgusty33's avatar
      mgusty33
      Frequent Visitor
      8 years ago

      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's avatar
        mgusty33
        Frequent Visitor
        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.