Forum Discussion

carlosarmpb's avatar
carlosarmpb
Frequent Visitor
8 years ago
Solved

Verify Cross-Selling

 

Hello!

 

I'm starting at PowerBI.

 

I have a column with the sales codes and another with the code for my product.

I want to filter through the product code and have all sales codes that have this code listed in the table. But I also want the same table to show the product codes.

 

I do not know if I use stockings or just filter.

 

 

Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    carlosarmpb

     

    Hi, One Way is this:

     

    Step 1: Add a New Table with Cod Prod

     

    Modeling - New Table

     

    CodProdTable = DISTINCT(Table1[Cod Prod])

     

     Step 2

     

    Add a measure

     

    FilterM =
COUNTROWS (
    SUMMARIZE (
        FILTER (
            Table1;
            CALCULATE (
                COUNT ( Table1[Cod Prod] );
                FILTER (
                    ALLEXCEPT ( Table1; Table1[Sales Code] );
                    Table1[Cod Prod] = SELECTEDVALUE ( CodProdTable[Cod Prod] )
                )
            )
                > 0
        );
        Table1[Sales Code]
    )
)

    Step 3: And in Visual Level Filter Add this Measure To Is Not Blank

     

  • Vvelarde's avatar
    Vvelarde
    8 years ago

    carlosarmpb

     

    Another Way is Replacing Step 2 with.

     

     

    FilterM =
COUNTROWS (
    INTERSECT (
        CALCULATETABLE (
            VALUES ( Table1[Cod Prod] );
            ALLEXCEPT ( Table1; Table1[Sales Code] )
        );
        VALUES ( CodProdTable[Cod Prod] )
    )
)

    Regards

     

    Victor

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi carlosarmpb,

     

    Based on the image you are showing if you select on the slicer the code 1000 the table will be filter out for the products where prod_code is 1000.

     

    What is your expected result and what are your particular requests.

     

    Regards,

    MFelix

    • carlosarmpb's avatar
      carlosarmpb
      Frequent Visitor
      8 years ago

      Yeah, that's fine.

       

      But I want this table also to show all the product codes that have the same sales code.

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        8 years ago

        carlosarmpb

         

        Hi, One Way is this:

         

        Step 1: Add a New Table with Cod Prod

         

        Modeling - New Table

         

        CodProdTable = DISTINCT(Table1[Cod Prod])

         

         Step 2

         

        Add a measure

         

        FilterM =
COUNTROWS (
    SUMMARIZE (
        FILTER (
            Table1;
            CALCULATE (
                COUNT ( Table1[Cod Prod] );
                FILTER (
                    ALLEXCEPT ( Table1; Table1[Sales Code] );
                    Table1[Cod Prod] = SELECTEDVALUE ( CodProdTable[Cod Prod] )
                )
            )
                > 0
        );
        Table1[Sales Code]
    )
)

        Step 3: And in Visual Level Filter Add this Measure To Is Not Blank