DAX filter help needed

[harshadrokade]

Hi,

I have table as Vendordata (See at bottom). There is other table which has Vendor names as well (Vendorlist) & a slicer is created based on Vendor name from Vendorlist table. Vendordata & Vendorlist tables are connected with each other with Vendor name only.

I have created a card visual & I want to see how many clients using selected vendor in slicer as Secondary vendor. 

E.g. If I select ABC4 in the slicer, I want the card visual to show count as 4 becasue there are 4 clients using it as Secondary vendor. If I select ABC6 in the slicer, I want the card visual to show count as 3.

I have created a dax but since two tabes connected with Vendor name only & I have slicer as Vendor name, it is filtering the table & showing count as blank. Pls help sir.

MS-original1 = if (ISFILTERED ('Vendirlist'[Vendor name]),CALCULATE(COUNT('Vendordata'[Secondary Vendor]),ALLEXCEPT('Vendordata','Vendordata'[Secondary Vendor]),'Vendordata'[Secondary Vendor]=SELECTEDVALUE('Vendorlist'[Vendir name])),"NA")

Table Vendordata-

Client ID	Vendor	Secondary Vendor	Service	Service used?
1	ABC1	ABC4	XYZ1	Yes
2	ABC1	ABC4	XYZ1	Yes
3	ABC1	ABC4	XYZ1	Yes
4	ABC1	ABC4	XYZ2	Yes
5	ABC4	ABC5	XYZ2	Yes
6	ABC4	ABC5	XYZ3	Yes
7	ABC5	ABC6	XYZ3	Yes
8	ABC2	ABC6	XYZ3	No
9	ABC7	ABC6	XYZ3	No

[AlexisOlson · Accepted solution]

Does this work?

MS-original1 =
VAR SelectedVendor = SELECTEDVALUE ( 'Vendorlist'[Vendor name] )
RETURN
    CALCULATE (
        COUNT ( 'Vendordata'[Secondary Vendor] ),
        ALL ( 'Vendorlist' ),
        'Vendordata'[Secondary Vendor] = SelectedVendor
    )

[harshadrokade]

Thanks a lot sir. Appreciate your help.

[harshadrokade]

HI @Ashish_Mathur  Can youp pls help me on above. Its the smilar table for which you helped me on last query.

[Ashish_Mathur]

Hi,

Transform data the way i did in the previous question.