Seems like a LOOKUPVALUE problem?

[Anonymous]

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_memberrecordid	poc_departmentname
12345	Supermarket
12340	Restaurant
12349	Cosmetic
12348	Cosmetic
12348	Cloth
12346	Restaurant
12343	Restaurant
12344	Resaurant

The result I expected will be like this:

poc_memberrecordid	poc_departmentname
12349	Cosmetic
12348	Cosmetic
12348	Cloth

Thanks!

[v-juanli-msft · Accepted solution]

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

[Ashish_Mathur]

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]

Hi,

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

[Ashish_Mathur]

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.

[Anonymous]

The second table:

poc_memberrecordid	poc_departmentname
12349	Cosmetic
12348	Cosmetic
12348	Cloth

[Ashish_Mathur]

Hi,

See if my solution here helps - Filter a column of a Pivot Table on a certain condition but also show other items from that column.