List by Open Status

[abhilash_sa]

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!

[manikumar34 · Accepted solution]

@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]

    )

RETURN

    FILTER(

        'Table (2)',

        NOT (

            'Table (2)'[Product] & 'Table (2)'[Type]

            IN SELECTCOLUMNS(ClosedTypes, "Key", [Product] & [Type])

        )

    )

[Khushidesai0109 · Accepted solution]

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]

Exactly what I needed! Thank you very much!

[manikumar34]

@abhilash_sa , 
You can filter Status to Open on visual level filter. 

[abhilash_sa]

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.

[manikumar34 · Accepted solution]

@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]

    )

RETURN

    FILTER(

        'Table (2)',

        NOT (

            'Table (2)'[Product] & 'Table (2)'[Type]

            IN SELECTCOLUMNS(ClosedTypes, "Key", [Product] & [Type])

        )

    )

[abhilash_sa]

Thank you very much. Exactly the solution I needed.

[Deku]

Add this to the filter pane of the table visual and filter to equals 0

HasClosed =
CALCULATE( 
   COUNTROWS( 'Table 2' )
   ,'Table 2'[Status] = "Closed"
)