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] )
| 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.