DAX Column value conditional on multiple rows

[Anonymous]

I have a joined table of opportunities and products grouped by product line, e.g. 

Opp | Product Line

A     |   X

B     |   Y

C     |   X

C     |   Y

D    |   Z

I'm interested in being able to filter on the Opportunities like C, where they contain two specific product lines (X and Y). All assistance appreciated.

[v-xjiin-msft]

@Anonymous

If you want to filter your table, you can simply use a slicer. Please refer:

If above sample doesn't satisfy your requirement, please kindly elaborate your requirement with detailed description and your desired result.

Thanks,
Xi Jin.

[Anonymous]

I'm trying to find opportunies like C... i.e. opportunities that contain both product line X and product line Y. The Opportunity column is in reality an Opp ID not a category of opportunity. Perhaps it would be clearer to say that producing a category to filter on is what i'm trying to do.

Opp | Product Line | OppContainsXandY

 A    |         X           |            No

 B    |         Y           |            No

C    |         X           |            Yes

C    |         Y           |            Yes

D    |         Z           |            No

E    |         Y           |            Yes

E    |         X           |            Yes

F    |         X           |            No

F    |         Z           |            No

G    |         X           |            Yes

G    |         Y           |            Yes

G    |         Z           |            Yes

What is the DAX to create the column OppContainsXandY?

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

Try this Column

OppContainsXandY =
VAR Mytable =
    FILTER ( TableName, TableName[Opp] = EARLIER ( TableName[Opp] ) )
RETURN
    IF (
        CONTAINS ( mytable, TableName[Product Line], "X" )
            && CONTAINS ( mytable, TableName[Product Line], "Y" ),
        "Yes",
        "No"
    )

[Anonymous]

Hi @Zubair_Muhammad

Your solution works great - thank you!

[Zubair_Muhammad]

@Anonymous