Find and count duplicates for Column A in Table 1 using data from Column A in Table 2

[trdoan]

Hi everyone,

I have the following tables and columns:

1. "Compare Data" table:

Name	Model	Material No	Group	Cost
A	W5	1005-01	EEC	100
A	W5	1005-02	EEC	150
A	W3	1005-03	DIVIDER	105
A	W3	1005-04	DIVIDER	3000
A	W5	1005-05	EEC	1215

2. "Full Data" table

Vendor Name	Engine Model	Material Number	Material Group	Repair Cost
A	W5	1005-01	EEC	80
B	W5	1005-02	EEC	85
C	W3	1005-03	DIVIDER	135
D	W3	1005-04	DIVIDER	3005
E	W5	1005-05	EEC	1115
F	W2	1005-08	EEC	250
A	W5	1005-09	EEC	350
A	W5	1005-03	EEC	135
A	W5	1005-25	DIVIDER	125
B	W3	1005-2	DIVIDER	125.5
B	W15	1005-3	EEC	135
R	W5	1005-2	EEC	450

"Compare Data" table only has 1 vendor name while "Full Data" table has all names. 

My questions are:

1. I'm looking to find and count all alternatives to A from the "Full Data" table who are capable of repairing a certain model (in a sense that if I use "Model" column as a slicer and one or more selections are made). In other words, other than A, what are the other vendors from the "Full Data" table that can repair the W5, W3, W15,... model for example? 

Desired Result: For W5, vendors different from A = E, B, R . I'd like to exclude A from the result as I only want to see any vendors other than A.

2. Is it possible to show A's repair cost of a model using data from the "Compare Data" table and those of alternative vendors, all displaying in a Bubble Chart and at the same time the chart reponses to a slicer that users can choose whichever models or material groups they prefer?

Could you please show me how to do these? Thank you so much for your helps!

[Zubair_Muhammad]

@trdoan

you can use this MEASURE

See file attached

Measure =
VAR mymodel =
    VALUES ( 'Compare Data'[Model] )
RETURN
    CONCATENATEX (
        EXCEPT (
            CALCULATETABLE (
                VALUES ( 'Full Data'[Vendor Name] ),
                'Full Data'[Engine Model] IN mymodel
            ),
            VALUES ( 'Compare Data'[Name] )
        ),
        [Vendor Name],
        ", "
    )

[Anonymous]

Hi @Zubair_Muhammad

Thanks for your help! It works great! However, it seems to only response to "Model" slicer but not both "Model" & "Group" slicers? Is it possible to make it work out for both?

I also have 2 others question:

1. How would you show the highest and the lowest-cost vendors using 2 Card Visuals. Like creating 2 measures where they can show who is the highest-cost vendor and same for lowest-cost vendor?

2. Having known which suppliers are capable of repairing a certain engine (by using your measure), can you please also help me create something to use for a Bubble Chart where it can show A's Average Cost and those of alternative suppliers?

I can't seem to get the measure to work in the Bubble Chart and I think my case needs a calculated column of some sort?

Do you know how I can get it done?

Thanks again for your help!

[Zubair_Muhammad · Accepted solution]

@Anonymous@trdoan

sorry for late reply. I had been travelling

Please try this Revised MEASURE and see file attached as well

Measure =
VAR mymodel =
    VALUES ( 'Compare Data'[Model] )
VAR mygroup =
    VALUES ( 'Compare Data'[Group] )
RETURN
    CONCATENATEX (
        EXCEPT (
            CALCULATETABLE (
                VALUES ( 'Full Data'[Vendor Name] ),
                FILTER (
                    'Full Data',
                    'Full Data'[Engine Model] IN mymodel
                        && 'Full Data'[Material Group] IN mygroup
                )
            ),
            VALUES ( 'Compare Data'[Name] )
        ),
        [Vendor Name],
        ", "
    )

For Highest Cost Vendor,,use this MEASURE

Highest cost vendor =
VAR mymodel =
    VALUES ( 'Compare Data'[Model] )
VAR mygroup =
    VALUES ( 'Compare Data'[Group] )
RETURN
    CONCATENATEX (
        TOPN (
            1,
            EXCEPT (
                CALCULATETABLE (
                    VALUES ( 'Full Data'[Vendor Name] ),
                    FILTER (
                        'Full Data',
                        'Full Data'[Engine Model] IN mymodel
                            && 'Full Data'[Material Group] IN mygroup
                    )
                ),
                VALUES ( 'Compare Data'[Name] )
            ),
            CALCULATE ( SUM ( 'Full Data'[Repair Cost] ) ), DESC
        ),
        [Vendor Name],
        ", "
    )

For lowest cost vendor

Lowest cost vendor =
VAR mymodel =
    VALUES ( 'Compare Data'[Model] )
VAR mygroup =
    VALUES ( 'Compare Data'[Group] )
RETURN
    CONCATENATEX (
        TOPN (
            1,
            EXCEPT (
                CALCULATETABLE (
                    VALUES ( 'Full Data'[Vendor Name] ),
                    FILTER (
                        'Full Data',
                        'Full Data'[Engine Model] IN mymodel
                            && 'Full Data'[Material Group] IN mygroup
                    )
                ),
                VALUES ( 'Compare Data'[Name] )
            ),
            CALCULATE ( SUM ( 'Full Data'[Repair Cost] ) ), ASC
        ),
        [Vendor Name],
        ", "
    )

[Anonymous]

Hi @Zubair_Muhammad , thank you thank you thank you! They worked perfectly! Only that I had to change the SUM into AVG and && to OR.

BUT THANK YOU SO MUCH!!!