Changing context

[Hezekiah]

I have two tables, Person and Product Usage. The Person table has a Person ID field. Product Usage Table has two fields: Person ID and Product. Each record in the Product Usage table indicates which product a person is using. The person may have many rows in the Product Usage table. There is a two way, one-to-many relationship betwen the Person and Product Usage tables, based on the Person ID.

When using a slicer based on products, I would like to calculate how many people using one product are also using another product. For example, I select product A, which narrows down my list of people to 25. Of those 25, how many are also using product B?

I'm just not certain how to go about doing this. When I filter the product table based on a product, so I can identify which people are using that product, it completely removes all other products. So, when I reach back into the Product Usage table from the Person table, the only products visible are the ones I am filtering for. I feel confident that I need to somehow change the contect of that query back into the Product Usage table, but I can't quite figure out how to do it.

Can anyone help?

[Anonymous]

Hi @Hezekiah,

Nope, when you use product usage table as source of slicer and selection multiple items. It will filter users who has single item from selected list or has multiple products.

For this scenario, you need to write a measure to check row contents and return tag, then use tag as filter condition apply on visuals to display users who has multiple products at same time.(create new table with unique products and not has relationship to original table, write measure to get selection products and compare with current row contents to return tag, drag measure to visual level filter to filter not matched records)

If you are confused on coding formula, please share some sample data for test.

Regards,

Xiaoxin Sheng

[Hezekiah]

Hello, @Anonymous. Thank you so much for the feedback. It's very helpful!

It appears that this challenge is currently out of the range of my skill level. If you have time, I would appreciate further elaboration. The functions to use, and perhaps a formula would indeed be very helpful for me.

Let's say that I have the following data structure:

Table: Person

Field: ID

Table: Product Usage

Field: PersonID

Field: Product Used

The values in the [Product Used] table could be Product 1 and Product 2.

I am every grateful for your help. Some general direction on what functions to use would be excellent. Thanks!

[Anonymous]

Hi @Hezekiah,

If you can please share a pbix file with some sample file to test and code formula.

Regards,

Xiaoxin Sheng

[Hezekiah]

Yes, of course. I appreciate ypour help very much.

I don't see a file sharing function built into the forum, so here is a link to a Dropbox file:

Here is a file.

[Hezekiah]

Wow, that's outstanding. Thank you so much for your help!

[Anonymous · Accepted solution]

Hi @Hezekiah,

When I test on your data, I found a simply way to achieve your requirement, please try to use below measure formulas:

Count of All Purchased = 
COUNTROWS (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Product Usage' ),
            [ID],
            "Count", COUNT ( 'Product Usage'[Product] )
        ),
        [Count] = COUNTROWS ( ALLSELECTED ( 'Product Usage'[Product] ) )
    )
)

All Purchased Customer list = 
CONCATENATEX (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Product Usage' ),
            [ID],
            "Count", COUNT ( 'Product Usage'[Product] )
        ),
        [Count] = COUNTROWS ( ALLSELECTED ( 'Product Usage'[Product] ) )
    ),
    [ID],
    ","
)

Regards,

Xiaoxin Sheng