topic Re: Check if quantity equals in rows with same id in Report Server

Check if quantity equals in rows with same id

dshmulenson — Sun, 25 Feb 2024 11:02:51 GMT

Hi,

im currenty using report server and i have this table:

Item ID	Quantity	Status
1	2	available
1	2	available
3	3	Issue
3	4	invalid

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

Re: Check if quantity equals in rows with same id

lbendlin — Sun, 25 Feb 2024 16:27:50 GMT

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

Re: Check if quantity equals in rows with same id

Anonymous — Tue, 27 Feb 2024 02:50:38 GMT

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.