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 hav...
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

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

     

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    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