Forum Discussion
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.
Thank you so much!
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.
7 Replies
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.
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.
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
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.
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 !
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
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?!
