Filter / Query

[Nosh10]

Hello, 

After filtering on a product, I would like to show all customers that purchased that product in a table. Based upon the results, I  would like to see what other products those customers purchased in another table in the report. 

Example:

1. Select product A from filter

2. Click on a customer name based on the results from the filter 

3. Display all products that the customer purchased including product A in a different table 

* Product A is a mandatory selection 

Thank you. 

[Nosh10]

[v-lid-msft]

Hi, @Nosh10 

First we make a calculate table that copy the data table to ignore the filter on pear

DataCopy = Data

then create a measure to control the show/hide of row.

Status2 =
IF (
    SELECTEDVALUE ( DataCopy[Customer ID] )
        IN CALCULATETABLE ( VALUES ( Data[Customer ID] ) ),
    1,
    BLANK ()
)

At last, use the column in datacopy table to create the table you want

Then with the feature of Sync Slicer, you can put the table into another page.

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[Nosh10]

@v-lid-msft 

Thank you. 

The DataCopy = Data   calculation is not working. Please note, as I forgot to mention, I'm using DirectQuery, not import. Not sure if this is why I'm not able to copy the table. I copied the table in edit query, but the table doesn't show in the report 

[v-lid-msft]

Hi @Nosh10 ,

Have you changed the table name in formula to your table? The copy of table using DirectQuery works fine on my side.

But you have trouble doing this you can try the workaround that connect to the same table using DirectQurey again to get a same table, it should work as the same as the calculate table in the following picture.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[Nosh10]

@v-lid-msftYes I changed the table name in the edit query and it's still not showing in the report 

[v-lid-msft]

Hi @Nosh10 ,

Create the calculate table is not supported in DirectQuery Mode. You can try the workaround that connect the datasource again.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution to a similar problem here - Identify Customers that Organisations can upsell or cross sell their products to.

[amitchandak]

If customers are coming from a transactional Table and product is coming from dimension, it will happen automatically.

If both are coming from different tables, Try bi-directional join. You have the option on relation property.

[Anonymous]

Hi @Nosh10 

Please provide some sample data and expected results.

Thanks

A