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

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