Forum Discussion

Hezekiah's avatar
Hezekiah
Frequent Visitor
7 years ago
Solved

Changing context

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Hezekiah
Frequent Visitor
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Hezekiah,

     

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

     

    Regards,

    Xiaoxin Sheng

    • Hezekiah's avatar
      Hezekiah
      Frequent Visitor
      7 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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