Get new list based on filtration

[asust9]

Hi,

I'm struggling with a data selection / filtration query. Not sure if I should use some kind of dynamic tables or measures to solve this filtration.

The data table has 3 columns order number, part no and name of part no (product name).
When selecting a part no, for instance "A" I want to show all product names from orders that contain part no "A" with a count. The selected part no / product should be excluded from the result.

Anyone has any idea how to perform this? Appreciate all help!

[v-yanjiang-msft · Accepted solution]

Hi @asust9 ,

According to your description, here's my solution.

1.Create a new table, don't make relationship between the two tables.

Table 2 = VALUES('Table'[Product name])

2.Create two measures.

Check =
VAR _T =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Order number]
            IN SELECTCOLUMNS (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Part no] = SELECTEDVALUE ( 'Table'[Part no] )
                ),
                "Order no", 'Table'[Order number]
            )
    )
RETURN
    IF (
        MAX ( 'Table 2'[Product name] )
            IN SELECTCOLUMNS ( _T, "Order no", [Product name] )
                && NOT (
                    MAX ( 'Table 2'[Product name] )
                        IN SELECTCOLUMNS (
                            FILTER (
                                ALL ( 'Table' ),
                                'Table'[Part no] = SELECTEDVALUE ( 'Table'[Part no] )
                            ),
                            "Pro name", 'Table'[Product name]
                        )
                ),
        1,
        0
    )

Order count =
VAR _T =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Order number]
            IN SELECTCOLUMNS (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Part no] = SELECTEDVALUE ( 'Table'[Part no] )
                ),
                "Order no", 'Table'[Order number]
            )
    )
RETURN
    COUNTROWS ( FILTER ( _T, [Product name] = MAX ( 'Table 2'[Product name] ) ) )

3.Put the Product name from the new table and Order count measure in a visual, put the Check measure in the visual filter and select its value to 1, get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.