Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with Count based on another column

I have an example data set:

 

IDTypeQuantity
1IP1
2IP1
2OP7
3IP1

 

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?

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

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

8 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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] )
  • KNP's avatar
    KNP
    Super User
    4 years ago

    Hi Anonymous,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I want to ignore the IP rows, but sub the OP rows in whihc the ID was also associated with IP 

      • KNP's avatar
        KNP
        Super User
        4 years ago

        Not sure I completely follow.

        Does the measure I provided do that?