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'm new to PowerBI, so any help would be greatly appreciated! I have attached a table below as an example.
Invoice ID | Invoice line ID | Category | Wanted |
1 | 1 | A | A |
1 | 2 | B | A |
1 | 3 | A | A |
1 | 4 | A | A |
2 | 5 | A | A |
2 | 6 | B | A |
2 | 7 | C | A |
3 | 8 | B | Not A |
3 | 9 | B | Not A |
4 | 10 | B | Not A |
4 | 11 | B | Not A |
4 | 12 | C | Not A |
4 | 13 | C | Not A |
I have some invoices which need to be categorized. The "Wanted" column contains an example of the required output.
In the table, there are 4 unique invoices (4 unique IDs). An invoice consists of multiple invoice lines (13 unique IDs). For example, Invoice ID 2 contains 3 Invoice lines, with Invoice line ID 5, 6 and 7. For each Invoice ID, I would like to check whether one or multiples rows contain Category "A". If that is the case, the output should be "A", otherwise it should be "Not A".
For example, Invoice ID 1 contains three rows with Category "A", so the wanted output should be "A". Invoice ID 2 contains one row with Category "A", so the output should also be "A". Invoice ID 3 and 4 contain no row with Category "A", so the output should be "Not A".
Thanks in advance!
Solved! Go to Solution.
@Anonymous
You can add a calculated column to your table with this code.
Wanted =
VAR _ACount =
CALCULATE(
COUNTROWS('Table'),
'Table'[Category]="A"
,ALLEXCEPT('Table','Table'[Invoice ID])
)
RETURN
IF ( _ACount > 0, "A", "Not A")
@Anonymous
You can add a calculated column to your table with this code.
Wanted =
VAR _ACount =
CALCULATE(
COUNTROWS('Table'),
'Table'[Category]="A"
,ALLEXCEPT('Table','Table'[Invoice ID])
)
RETURN
IF ( _ACount > 0, "A", "Not A")
It works perfectly. Your help is greatly appreciated, you saved me a lot of work. Thanks a lot!
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |