cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trdoan Member
Member

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

Hi everyone,

 

I have the following tables and columns:

 

1. "Compare Data" table:

NameModelMaterial NoGroupCost
AW51005-01EEC100
AW51005-02EEC150
AW31005-03DIVIDER105
AW31005-04DIVIDER3000
AW51005-05EEC1215

 

2. "Full Data" table

Vendor NameEngine ModelMaterial NumberMaterial GroupRepair Cost
AW51005-01EEC80
BW51005-02EEC85
CW31005-03DIVIDER135
DW31005-04DIVIDER3005
EW51005-05EEC1115
FW21005-08EEC250
AW51005-09EEC350
AW51005-03EEC135
AW51005-25DIVIDER125
BW31005-2DIVIDER125.5
BW151005-3EEC135
RW51005-2EEC450

 

"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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

@iamtrangdoan@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],
        ", "
    )

 

 

4 REPLIES 4
Super User
Super User

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

@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],
        ", "
    )

saddas.png

iamtrangdoan Regular Visitor
Regular Visitor

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

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!

Super User
Super User

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

@iamtrangdoan@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],
        ", "
    )

 

 

iamtrangdoan Regular Visitor
Regular Visitor

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

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!!!