Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Seems like a LOOKUPVALUE problem?

Hi experts,
I have a table 'poc_memberrecords'

I want to pick all the 'poc_memberrecordid' containing 'Cosmetics' in 'poc_departmentname'. (but not only containing 'Cosmetics' as the same 'poc_memberrecordid' may also have purchased 'restaurant' or 'cloth'...etc)
How should I do this?

 

poc_memberrecords

poc_memberrecordidpoc_departmentname
12345Supermarket
12340Restaurant
12349Cosmetic
12348Cosmetic
12348Cloth
12346Restaurant
12343Restaurant
12344Resaurant

 

The result I expected will be like this:

poc_memberrecordidpoc_departmentname
12349Cosmetic
12348Cosmetic
12348Cloth

Thanks!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    Create a measure 

    Measure = IF(NOT(ISERROR(FIND("Cosmetic",MAX([poc_departmentname])))),1,0)

    Create a new table

    Table =
VAR filtered1 =
    FILTER (
        SUMMARIZE ( poc_memberrecords, poc_memberrecords[poc_memberrecordid] ),
        [Measure] = 1
    )
RETURN
    FILTER ( poc_memberrecords, [poc_memberrecordid] IN filtered1 )

     

     

     

    Best Regards

    Maggie

     

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    The entries in the poc_departmentname of the first table are not in English.  The entries in the poc_memberrecordid column are absolutely different.  Neither do i understnad yoru question nor the expected result.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

      I've revised it and it should be easier to understand now.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        Hi,

         

        I still do not understand.  In the first table there is supermarket appearing against 12345.  Why in the second table is there 12345 appearing 3 times with Cosmetics, Clothes and Restaurant.  I just do not understand.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    Create a measure 

    Measure = IF(NOT(ISERROR(FIND("Cosmetic",MAX([poc_departmentname])))),1,0)

    Create a new table

    Table =
VAR filtered1 =
    FILTER (
        SUMMARIZE ( poc_memberrecords, poc_memberrecords[poc_memberrecordid] ),
        [Measure] = 1
    )
RETURN
    FILTER ( poc_memberrecords, [poc_memberrecordid] IN filtered1 )

     

     

     

    Best Regards

    Maggie