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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mgusty33
Frequent Visitor

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?

 

2018-03-27_9-30-15.jpg

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

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-juanli-msft
Community Support
Community Support

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.

26.png

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

27.png

Best regards

Maggie

 

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? 

Hi

Hope this will help

in Query Editor, add a conditional column as follows

28.png

 

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

29.png

 

 

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. 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.