cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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
Highlighted
Super User III
Super User III

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

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

 

 

Regards,
Zubair


View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

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

Regards,
Zubair


Highlighted
Anonymous
Not applicable

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!

Highlighted
Super User III
Super User III

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

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

 

 

Regards,
Zubair


View solution in original post

Highlighted
Anonymous
Not applicable

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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors