topic DAX to count number of rows with multiple conditions in DAX Commands and Tips

DAX to count number of rows with multiple conditions

JajatiDev — Fri, 13 May 2022 22:25:04 GMT

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

Re: DAX to count number of rows with multiple conditions

Jihwan_Kim — Sat, 14 May 2022 02:59:25 GMT

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 )

Re: DAX to count number of rows with multiple conditions

JajatiDev — Mon, 16 May 2022 22:22:47 GMT

Hi Jihwan_Kim,

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

Regards,

Jajati Dev