Forum Discussion
Solved
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 ...
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!
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])))Thank you very much. Exactly the solution I needed.
