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.
I have 3 columns:
Invoice ID
Product ID
IS AH
Obviously, there are multiple products on one invoice. If there is a specific product on an invoice (product 'Z' in example below), IS AH gives me a yes for that specific line.
I need a column that will give me a yes for all the other lines on any invoice that has a "yes" in column "IS AH".
Any suggestions?
Example below
Solved! Go to Solution.
@Anonymous,
Thank you for the sample. Here is my solution:
You can create 2 calculated columns as follow:
ISAH Value = IF(Table10[IS AH] = "No",0,1)
NEEDED Column = VAR ISAH_value_by_invoice = CALCULATE(SUM(Table10[ISAH Value]),
FILTER(Table10,Table10[Invoice ID] = EARLIER(Table10[Invoice ID]))) RETURN IF(ISAH_value_by_invoice = 0,"No","Yes")
Here is the result:
Let me know if this helps.
@Anonymous,
If you can provide a sample data and the output you want, it will be clearer. Otherwise, I don't know why you want this column for if have an invoice with 1 product, I think it should not have other lines but only 1?
Updated with example.
Issue is that there are MANY products per invoice.
@Anonymous,
Thank you for the sample. Here is my solution:
You can create 2 calculated columns as follow:
ISAH Value = IF(Table10[IS AH] = "No",0,1)
NEEDED Column = VAR ISAH_value_by_invoice = CALCULATE(SUM(Table10[ISAH Value]),
FILTER(Table10,Table10[Invoice ID] = EARLIER(Table10[Invoice ID]))) RETURN IF(ISAH_value_by_invoice = 0,"No","Yes")
Here is the result:
Let me know if this helps.
THANK YOU! Genius work-around!
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |