Forum Discussion

trdoan's avatar
trdoan
Icon for Helper III rankHelper III
7 years ago
Solved

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: Name Model Material No Group Cost A W5 1005-01 EEC 100 A W5 1005-02 EEC 150 A W3 1005...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymoustrdoan

     

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymoustrdoan

     

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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