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

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

    • Hezekiah's avatar
      Hezekiah
      Frequent Visitor
      7 years ago

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