Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
excuse the potential stupid title, I couldn't come up with a better explanation.
I have a table containing all items on our invoices, i.e. if a invoice has two line items, i have two rows with identical invoice numbers but different line item details.
I'd like to create a new column (True/False) for the following:
If an Invoice (single invoice number, multiple line items) contains A and B then true
If it contains only A then false
if the logic does not apply (becasue there is no A on the invoice) the "N/A"
I'm not bound to TRUE/FALSE, can be also text like "OK", "Not OK" and "N/A" or whatever is suitable.
I searched the net and asked Aunt Google but couldn't find anything related to this.
Any help is highly appreciated.
thanks
Solved! Go to Solution.
Hi @adieball,
According to your description above, you should be able to use the formula below to create the column in your scenario.
Column = IF ( CALCULATE ( CONTAINS ( 'Table1', 'Table1'[line items], "A" ) && CONTAINS ( 'Table1', 'Table1'[line items], "B" ), ALLEXCEPT ( 'Table1', 'Table1'[invoice number] ) ), "OK", IF ( CALCULATE ( CONTAINS ( 'Table1', 'Table1'[line items], "A" ), ALLEXCEPT ( 'Table1', 'Table1'[invoice number] ) ), "Not OK", "N/A" ) )
Note: Just replace 'Table1' with your real table name.
Regards
Hi @adieball,
According to your description above, you should be able to use the formula below to create the column in your scenario.
Column = IF ( CALCULATE ( CONTAINS ( 'Table1', 'Table1'[line items], "A" ) && CONTAINS ( 'Table1', 'Table1'[line items], "B" ), ALLEXCEPT ( 'Table1', 'Table1'[invoice number] ) ), "OK", IF ( CALCULATE ( CONTAINS ( 'Table1', 'Table1'[line items], "A" ), ALLEXCEPT ( 'Table1', 'Table1'[invoice number] ) ), "Not OK", "N/A" ) )
Note: Just replace 'Table1' with your real table name.
Regards
thanks a lot. Will try this formula as soon as I can (need todo some other cleanup first).
Andre
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |