Forum Discussion

Harry_Tran's avatar
Harry_Tran
Helper III
5 years ago
Solved

Filter data

Hi everyone, I have a sample table like below. One Invoice can contain multiple Product_ID. What I am trying to do is find the Invoices that contain either 'A' or 'C' AND either 'Z123', 'Z234', or ...
Need Help
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    Hi, Harry_Tran 

    You can create a Measure and then create a Table visual.

     

    1 Invoice_meet_requirement =

    VAR t =

        FILTER (

            'Table',

            'Table'[Product_ID]

                IN { "A", "C" }

                || 'Table'[Product_ID] IN { "Z123", "Z234", "Z345" }

        )

    VAR num =

        COUNTROWS ( t )

    RETURN

    IF ( num > 1, "Meet the requirement", "DO NOT meet the requirement" )

     

    2 Create a Table with 'invoice' Field and drag 'invoice_meet_requirement' to its visual filter

     

    The result looks like this:

    Best Regards,

    Caiyun Zheng

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-cazheng-msft's avatar
v-cazheng-msft
Community Support
5 years ago

Hi, Harry_Tran 

You can create a Measure and then create a Table visual.

 

1 Invoice_meet_requirement =

VAR t =

    FILTER (

        'Table',

        'Table'[Product_ID]

            IN { "A", "C" }

            || 'Table'[Product_ID] IN { "Z123", "Z234", "Z345" }

    )

VAR num =

    COUNTROWS ( t )

RETURN

IF ( num > 1, "Meet the requirement", "DO NOT meet the requirement" )

 

2 Create a Table with 'invoice' Field and drag 'invoice_meet_requirement' to its visual filter

 

The result looks like this:

Best Regards,

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Harry_Tran's avatar
    Harry_Tran
    Helper III
    5 years ago

    Hi v-cazheng-msft 

    Based on your DAX I fix my Masure a bit a it work

    Here is my measure

    Meet_The_Requirement = 

            var _Prod1 = FILTER('Table','Table'[Product_ID] in {"A","B"})

            var _Prod@ = FILTER('Table','Table'[Product_ID] in {"Z123","Z234","z345"})

            var _Invoice = COUNTROWS(_Prod1) / COUNTROWS (_Prod2)

            var _Result = IF(COUNTROWS(_Prod2) =0,0,_Invoice)

    return IF(_Result > 0,"Meet the requirement","DO NOT meet the requirement")

    Thank you so much.

  • Harry_Tran's avatar
    Harry_Tran
    Helper III
    5 years ago

    Hi v-cazheng-msft 

    Thank you so much for your help.

    There is an issue when I try using your DAX is if I have an invoice sale 2 'A' products or sale 'Z123' and 'Z234'. It still consider 'Meet requirement'

    For example, I add one more invoice 8 to the table

    Invocie '8' missing 'A' or 'B' but still consider meeting requirement

    Thank you

    • v-cazheng-msft's avatar
      v-cazheng-msft
      Community Support
      5 years ago

      Hi, Harry_Tran 

      You can try the following Measure.

       

      Invoice_meet_requirement =

      VAR t1 =

          FILTER ( 'Table', 'Table'[Product_ID] IN { "A", "C" } )

      VAR t2 =

          FILTER ( 'Table', 'Table'[Product_ID] IN { "Z123", "Z234", "Z345" } )

      VAR invoice_t1 =

          DISTINCT ( SELECTCOLUMNS ( t1, "invoice", 'Table'[Invoice] ) )

      VAR invoice_t2 =

          DISTINCT ( SELECTCOLUMNS ( t2, "invoice", 'Table'[Invoice] ) )

      VAR union_t =

          COUNTROWS ( UNION ( invoice_t1, invoice_t2 ) )

      RETURN

          IF ( union_t > 1, "Meet the requirement", "DO NOT meet the requirement" )

       

      Best Regards,

      Caiyun Zheng

       

      Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.