Add filters to a column

Anonymous · ‎06-26-2023

Hello

I am trying to create a report where I need to filter the data based on another column.

Columns: Item, Quantity on hand, Quantity on sales order etc

Requirement: Only show results where Quantity on sales order is >.001 and Quantity on Sales order is > Quantity on hand.

Can someone please help me with this?

Thank you

Manoj_Nair · ‎06-26-2023

@AnonymousHi,

Try this...

If you would like to create a calculated table that only contains the rows that meet your criteria, here is a DAX expression that could do that:

FilteredData = 
FILTER (
    'Table', 
    'Table'[Quantity on sales order] > 0.001 && 'Table'[Quantity on sales order] > 'Table'[Quantity on hand]
)

If you want to create a measure, it might be a little more complex because measures are not intended to filter data but instead perform calculations based on filters. However, we can create a measure that calculates, for example, the total quantity on hand for items that meet your criteria.

Here is an example:

FilteredQuantityOnHand = 
CALCULATE (
    SUM ( 'Table'[Quantity on hand] ),
    'Table'[Quantity on sales order] > 0.001,
    'Table'[Quantity on sales order] > 'Table'[Quantity on hand]
)

If this post helps to find solution would be happy if you could mark my post as a solution and give it a thumbs up

Best regards

Manoj Nair

Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/