Forum Discussion

GA1993's avatar
GA1993
Icon for Helper II rankHelper II
5 years ago
Solved

Filter those items that contains certain text

Need some help.

 

I would like to create another table that gets its data from another table by applying some filters. One that I am trying to create a DAX formula for is... not including the line items that a certain field contains.

 

For example,

Filter (source table[PO Number] <> that contains "SB")

 

This is what I think of but I am not sure where to add it into my code below. Even so, changing it to the correct DAX formula.

 

 

Sales Order Tracking = 
DISTINCT(
    SELECTCOLUMNS(
        FILTER('source table,
                source table[Order reason] = "SO" 
        ),
            "Sales Order",'source table'[Order]
    )
)

 

Your help would be really appreciated!

Need Help
Show and Tell
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi GA1993 

     

    You are close. Try this code:

    Sales Order Tracking = 
DISTINCT (
    SELECTCOLUMNS (
        FILTER (
            'Table',
            'Table'[Plant] <> "1037"
                && 'Table'[Order reason] <> "SIM"
                && 'Table'[Order reason] <> "RTF"
                && FIND ( "SB", 'Table'[PO Number],, 0 ) = 0
        ),
        "Sales Order", 'Table'[Order]
    )
)

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    GA1993 I think:

    Sales Order Tracking = 
DISTINCT(
    SELECTCOLUMNS(
        FILTER('source table,
                FIND("SO",source table[Order reason],,0) <> 0 
        ),
            "Sales Order",'source table'[Order]
    )
)
    • GA1993's avatar
      GA1993
      Icon for Helper II rankHelper II
      5 years ago

      Hi thanks for replying. As for your code, I think it did the opposite. I would like to filter out those values that contain "SB" in the PO No. field.

       

      I tried using your code but this is what I got.

       

       

      Also, is there a way to add other filters aside from the "FIND" function? Below is my code but I cant seem to make it work.

       

       

      Sales Order Tracking = 
DISTINCT(
    SELECTCOLUMNS(
        FILTER('Z_CONS_03',
                Z_CONS_03[Plant] <> "1037"
                && Z_CONS_03[Order reason] <> "SIM"
                && Z_CONS_03[Order reason] <> "RTF"
),

                FIND("SB", Z_CONS_03[PO Number],,0) <> 0 
        ),
            "Sales Order",'Z_CONS_03'[Order]
)

       

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi GA1993 

     

    You are close. Try this code:

    Sales Order Tracking = 
DISTINCT (
    SELECTCOLUMNS (
        FILTER (
            'Table',
            'Table'[Plant] <> "1037"
                && 'Table'[Order reason] <> "SIM"
                && 'Table'[Order reason] <> "RTF"
                && FIND ( "SB", 'Table'[PO Number],, 0 ) = 0
        ),
        "Sales Order", 'Table'[Order]
    )
)

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • GA1993's avatar
      GA1993
      Icon for Helper II rankHelper II
      4 years ago

      I needed to change the <> to = to make it work, regardless thank you for the tip!