DAX to count number of rows with multiple conditions

[JajatiDev]

Hi

Here is my order table;

Order No	SO Lines	Shipping Condition
10001	10	Ship as available
10001	20	Ship as available
12002	10	Ship complete
12002	20	Ship complete
13001	10	Ship as available
13001	20	Ship as available
13001	30	Ship as available
14001	10	Ship as available

If the shipping condition is 'Ship as available' then each of the order lines is treated separately. While if the order has the shipping condition as 'Ship complete' then the order is treated as 1 and all order lines need to ship together. This means in the above table I have 7 order lines.

I need assistance with the measures;

1. that counts the number of order lines based on shipping conditions, and

2. the other that just gives me the sum total of the count of order lines

Thanks and Regards

[JajatiDev]

Hi Jihwan_Kim,

Thank you. The DAX function shared is working as expected.

Regards,

Jajati Dev

[Jihwan_Kim · Accepted solution]

Hi,

Please try the below measures, and check the attached pbix file.

Count based on the condition measure: = 
VAR available_table =
    FILTER ( Data, Data[Shipping Condition] = "Ship as available" )
VAR complete_table =
    FILTER (
        SUMMARIZE ( Data, Data[Order No], Data[Shipping Condition] ),
        Data[Shipping Condition] = "Ship complete"
    )
RETURN
    COUNTROWS ( available_table ) + COUNTROWS ( complete_table )

Count measure: = 
COUNTROWS( Data )