Forum Discussion

dshmulenson's avatar
dshmulenson
New Member
2 years ago
Solved

Check if quantity equals in rows with same id

Hi,

im currenty using report server and i have this table:

Item IDQuantityStatus
12available
12available
33Issue
34invalid

I would like to add a column which can identify if quantity is equals when its same "Item ID" AND checks if Status is "Available".

Earlier Function didnt work cause the table doesnt has logical order for this function.

Thank in Advanced

 

 

"Report Server"
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi dshmulenson ,

    Please try below steps:

    1. below is my test table

    Table:

    2. create a new column with below dax formula

    IsQuantityEqualForAvailableStatus =
VAR currentItemID = 'Table'[Item ID]
VAR currentQuantity = 'Table'[Quantity]
VAR availableItemsSameID =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Item ID] = currentItemID
            && 'Table'[Status] = "available"
    )
RETURN
    IF (
        COUNTROWS ( availableItemsSameID ) > 1
            && CALCULATE ( MIN ( 'Table'[Quantity] ), availableItemsSameID ) = currentQuantity
            && CALCULATE ( MAX ( 'Table'[Quantity] ), availableItemsSameID ) = currentQuantity,
        "Yes",
        "No"
    )

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    You should be able to group across these three columns and then return all the groups where the rowcount is greater than 1.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi dshmulenson ,

    Please try below steps:

    1. below is my test table

    Table:

    2. create a new column with below dax formula

    IsQuantityEqualForAvailableStatus =
VAR currentItemID = 'Table'[Item ID]
VAR currentQuantity = 'Table'[Quantity]
VAR availableItemsSameID =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Item ID] = currentItemID
            && 'Table'[Status] = "available"
    )
RETURN
    IF (
        COUNTROWS ( availableItemsSameID ) > 1
            && CALCULATE ( MIN ( 'Table'[Quantity] ), availableItemsSameID ) = currentQuantity
            && CALCULATE ( MAX ( 'Table'[Quantity] ), availableItemsSameID ) = currentQuantity,
        "Yes",
        "No"
    )

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.