Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Harry_Tran
Helper III
Helper III

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 'Z345'. 

For example: Invoice '1' contain 'A' and 'Z123' -> meet the requirement.

                     Invoce '2' -> DO NOT meet the requirement

                     Invoice '3' contain 'A' and 'Z345' -> meet the requirement.

                     Invoice '5' contain 'Z234' but missing 'A' or 'B' -> DO NOT meet the requirement.

Harry_Tran_1-1611888916453.png

 

Thank you so much!

 

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

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

v-cazheng-msft_0-1612171118920.png

 

The result looks like this:

v-cazheng-msft_1-1612171118921.png

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.

View solution in original post

7 REPLIES 7
v-cazheng-msft
Community Support
Community Support

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

v-cazheng-msft_0-1612171118920.png

 

The result looks like this:

v-cazheng-msft_1-1612171118921.png

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.

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.

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

Harry_Tran_0-1612199196705.png

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

Harry_Tran_1-1612199286100.png

Thank you

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.

VijayP
Super User
Super User

@Harry_Tran 

I think you should create a column by concatinating bothe product and inv columns and use SWITCH Function.

Else Can you show me what is your final objective should be i mean the final outcome as a table?!

 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


VijayP
Super User
Super User

@Harry_Tran 

You Can use SWITCH Statement to find the required option. However if you can give me the outcome as a picture it is easy to give solution !




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Hi @VijayP ,

How can I use SWITCH in this situation when the Invoice is not unique and I need multiple choice form 1 column?

Thank you

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.