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 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. IE we would only sum ID 2 which would equal 7. What Dax measure could I create to do this?
Solved! Go to Solution.
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] )
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] )
Hi @billuran2009,
Depends on what you want to happen to the IP type rows.
Something like this?
[measure] =
CALCULATE (
SUM ( Table[Quantity] ),
Table[Type] = "OP"
)
Regards,
Kim
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
I want to ignore the IP rows, but sub the OP rows in whihc the ID was also associated with IP
Not sure I completely follow.
Does the measure I provided do that?
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
NO this will some all IDs that are OP, I want to some IDs that were IP but now OP.
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.
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |