How to filter a report to only see other values that match

[johnsgx5]

Hello,

I am trying to create a filter on a visualization where I want to see all values that exist if vendor 'A' has a bid (Price) on the part.

I don't want to only see vendor A's bids.

Is this possible?

Input				Output
Part	Vendor	Price		Part	Vendor	Price
131	A	$          95		131	A	$          95
132	B	$          85		131	B	$          97
133	C	$        100		131	C	$          90
134	D	$        120		132	B	$          85
131	B	$          97		132	A	$          87
131	C	$          90
132	A	$          87
137	C	$        150
138	D	$        145

[SachinNandanwar · Accepted solution]

First create a table that holds rows for Vendor A. The source table is Tbl_A.

Tbl_A = FILTER('Tbl_1','Tbl_1'[ Vendor]="A")

Then create a second table that filters rows from the main table based on values of Tbl_A

MatchingParts = 
SELECTCOLUMNS (
    FILTER('Tbl_1', 'Tbl_1'[Part] IN VALUES('Tbl_A'[Part])),
    "Part", 'Tbl_1'[Part],
    "Vendor", 'Tbl_1'[ Vendor],
    "Price", 'Tbl_1'[ Price]
)

[Anonymous · Accepted solution]

Hi @johnsgx5 

You can refer to the following solution.

1.Create a new table.

Table 2 = VALUES('Table'[Vendor])

2.Create a measure.

MEASURE =
VAR a =
    CALCULATETABLE (
        VALUES ( 'Table'[Part] ),
        'Table'[Vendor] IN VALUES ( 'Table 2'[Vendor] )
    )
RETURN
    CALCULATE ( SUM ( 'Table'[Price] ), 'Table'[Part] IN a )

Then put the vendor column in table2 to  a slicer, and put the measure and the related field of the original table to a table visual.

Output

Best Regards!

Yolo Zhu

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

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar question in the attached file.

Hope this helps.

[Anonymous · Accepted solution]

Hi @johnsgx5 

You can refer to the following solution.

1.Create a new table.

Table 2 = VALUES('Table'[Vendor])

2.Create a measure.

MEASURE =
VAR a =
    CALCULATETABLE (
        VALUES ( 'Table'[Part] ),
        'Table'[Vendor] IN VALUES ( 'Table 2'[Vendor] )
    )
RETURN
    CALCULATE ( SUM ( 'Table'[Price] ), 'Table'[Part] IN a )

Then put the vendor column in table2 to  a slicer, and put the measure and the related field of the original table to a table visual.

Output

Best Regards!

Yolo Zhu

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

[SachinNandanwar · Accepted solution]

First create a table that holds rows for Vendor A. The source table is Tbl_A.

Tbl_A = FILTER('Tbl_1','Tbl_1'[ Vendor]="A")

Then create a second table that filters rows from the main table based on values of Tbl_A

MatchingParts = 
SELECTCOLUMNS (
    FILTER('Tbl_1', 'Tbl_1'[Part] IN VALUES('Tbl_A'[Part])),
    "Part", 'Tbl_1'[Part],
    "Vendor", 'Tbl_1'[ Vendor],
    "Price", 'Tbl_1'[ Price]
)