Forum Discussion
List by Open Status
Hi,
I need help with deriving a table of open status list of products. I have 2 tables - a master with product names and another with open/closed reorder status.
Table 1
| Product |
| A1 |
| A2 |
| A3 |
| A4 |
Table 2
| Product | Type | Status |
| A1 | T1 | Open |
| A1 | T1 | Closed |
| A1 | T2 | Open |
| A1 | T2 | Closed |
| A1 | T3 | Open |
| A2 | T4 | Open |
| A2 | T5 | Open |
| A2 | T5 | Closed |
| A2 | T6 | Open |
| A2 | T7 | Open |
| A3 | T8 | Open |
| A3 | T9 | Open |
| A3 | T9 | Closed |
| A4 | Z1 | Open |
| A4 | Z2 | Open |
I want to be able to show only those product/type combinations with an open status, but no closed status, like below:
| Product | Type | Status |
| A1 | T3 | Open |
| A2 | T4 | Open |
| A2 | T6 | Open |
| A2 | T7 | Open |
| A3 | T8 | Open |
| A4 | Z1 | Open |
| A4 | Z2 | Open |
Filtering on Table 2 for "Open" will not work, because for the combination of A1,T1, while there is "Open", there is also a "Closed" status.
Thank you very much!
abhilash_sa ,
Got it, try below DAX.I have created a measure to filter the values. If you want to create a calcualted table then use the belwo DAX.
Table 3 =VAR ClosedTypes =SUMMARIZE(FILTER('Table (2)','Table (2)'[Status] = "Closed"),'Table (2)'[Product], 'Table (2)'[Type])RETURNFILTER('Table (2)',NOT ('Table (2)'[Product] & 'Table (2)'[Type]IN SELECTCOLUMNS(ClosedTypes, "Key", [Product] & [Type])))Hii abhilash_sa
This Might help you
Show_Open_Only =
VAR ClosedTypes =
CALCULATETABLE (
VALUES ( Table2[Type] ),
Table2[Status] = "Closed",
ALLEXCEPT ( Table2, Table2[Product] )
)
RETURN
IF (
Table2[Status] = "Open" &&
NOT Table2[Type] IN ClosedTypes,
"Show",
BLANK()
)If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!
7 Replies
abhilash_sa ,
You can filter Status to Open on visual level filter.Thank you for responding. unfortunately that will not work, because then this record will filter through:
A2 T5 Open but, it has a "Closed" record in Table 2. I want only those products that do not have a "Closed" status.
abhilash_sa ,
Got it, try below DAX.I have created a measure to filter the values. If you want to create a calcualted table then use the belwo DAX.
Table 3 =VAR ClosedTypes =SUMMARIZE(FILTER('Table (2)','Table (2)'[Status] = "Closed"),'Table (2)'[Product], 'Table (2)'[Type])RETURNFILTER('Table (2)',NOT ('Table (2)'[Product] & 'Table (2)'[Type]IN SELECTCOLUMNS(ClosedTypes, "Key", [Product] & [Type])))
Hii abhilash_sa
This Might help you
Show_Open_Only =
VAR ClosedTypes =
CALCULATETABLE (
VALUES ( Table2[Type] ),
Table2[Status] = "Closed",
ALLEXCEPT ( Table2, Table2[Product] )
)
RETURN
IF (
Table2[Status] = "Open" &&
NOT Table2[Type] IN ClosedTypes,
"Show",
BLANK()
)If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!Exactly what I needed! Thank you very much!
Add this to the filter pane of the table visual and filter to equals 0
HasClosed = CALCULATE( COUNTROWS( 'Table 2' ) ,'Table 2'[Status] = "Closed" )
