Forum Discussion
Help with Count based on another column
I have an example data set: ID Type Quantity 1 IP 1 2 IP 1 2 OP 7 3 IP 1 I want to sum the Quantity of OP types where the unique IDs have an IP and OP Line item. ...
How about this?
OP with IP = VAR CalcTable = ADDCOLUMNS ( VALUES ( Table01[ID] ), "@HasIP", "IP" IN CALCULATETABLE ( VALUES ( Table01[Type] ) ), "@QtyOP", CALCULATE ( SUM ( Table01[Quantity] ), Table01[Type] = "OP" ) ) RETURN SUMX ( FILTER ( CalcTable, [@HasIP] ), [@QtyOP] )
I think I got what you're after with my answer but it would help to include in your sample data rows that distinguish between just summing OP and summing OP that were previously IP. These are identical with your given example.
| ID | Type | Quantity |
| 1 | IP | 1 |
| 2 | IP | 1 |
| 2 | OP | 7 |
| 3 | OP | 1 |
| 4 | OP | 8 |
So in this example we owuld only want to sum ID 2, bc its the only one that was previously IP
Yeah, I got it. I'd recommend editing that into your original post though.
The DAX I suggested should work fine for this example.