Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

DAX Column value conditional on multiple rows

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.

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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"
    )

5 Replies

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

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

      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's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        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"
    )