How do I use a slicer filter and hasonevalue to filter another column

[nschmidt]

So this is pretty complicated so I hope I explain this correctly. I have two tables that are connected in a one to many relationship. (example data below). Because I'm in an SASS model I can't use selectedvalue but my goal is the following:

I have a slicer in the visualization for Transaction vendor and when I select "Jones" I want to be able to count the number of part numbers by Jones in the product table. I thought maybe this would work but I'm stuck:

CALCULATE(COUNTROWS('PRODUCT'),if(HASONEVALUE(SALES[Transaction Vendor Name]),filter('PRODUCT','PRODUCT'[Primary Vendor Name]=values(SALES[Transaction Vendor Name])),ALL('PRODUCT')))

Product -> Sales (part number is the key)

Part Number	Primary Vendor Name		Part Number	Transaction Vendor Name	Sales
AB	Jones		AB	Jones	$    10.00
AC	Jones		AB	Jones	$    15.00
AD	Ridge		AB	Jones	$    41.00
AE	Ridge		AF	Ridge	$  100.00
AF	Ridge		AF	Ridge	$    45.00
			AF	Ridge	$  145.00
			AF	Ridge	$    45.00

[v-robertq-msft · Accepted solution]

Hi, @nschmidt 

According to your description and sample data, I think that the problem exists in the relationship between your two tables because you said that the key between them is [Part Number], but you want to slice the value based on the selection of [Primary Vendor Name], which causes the filter problems, I suggest you to create a new table for the Slicer, you can try my steps:

1. Create a calculated table for Slicer:

Slicer =

SUMMARIZE('Sales',[Transaction Vendor Name] )

And don’t connect it with other tables:

2. Create a measure like this:

Count =

CALCULATE(

    COUNT('Product'[Part Number]),

    FILTER(

        ALL('Product'),

        [Primary Vendor Name]=VALUES(Slicer[Transaction Vendor Name])))

3. Then create a Slicer and place ‘Slicer’[Transaction Vendor Name], and a card chart to place the measure, like this:

And you can get what you want.

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[amitchandak]

@nschmidt , I not sure on need of all product , so add that if needed

try like

CALCULATE(COUNTROWS('PRODUCT'),filter('PRODUCT','PRODUCT'[Primary Vendor Name] in values(SALES[Transaction Vendor Name]) && HASONEVALUE(SALES[Transaction Vendor Name])))

[nschmidt]

I'm getting the following error message: