Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
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
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
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" )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |