topic Re: Count rows with filters from another table in DAX Commands and Tips

Count rows with filters from another table

joupuma — Tue, 30 Jun 2020 09:35:49 GMT

Good Morning,

I have a problem and I don't know how to solve it.

I have 2 tables, the first with all the production data and the second with a single column where there are the days that are holidays and weekends. The days that are working days do not appear in that second table.

In the first table I have 2 fields that are dated, the first the Date when the order was placed, and the other when the product must be delivered to the customer.

The goal of everything, is that I have to get to know the number of working days between the 2 dates.

I already have the part of the difference between the dates, but I don't know how to subtract the number of Holidays that come from the other Table.
I have tried it with the COUNTROWS and FILTER functions, but I cannot solve it ...

Could you help me?

Thanks and greetings,

Re: Count rows with filters from another table

AllisonKennedy — Tue, 30 Jun 2020 10:00:17 GMT

Do you also have a DimDate table? You will need one for this problem, then you just need to relate the Holidays to the date table, and FILTER the DimDate for Working Days and do a COUNTROWS on that in combination with the DATEDIFF.

Re: Count rows with filters from another table

Anonymous — Tue, 30 Jun 2020 11:30:53 GMT

// T1 with prod data: OrderDate, DeliveryDate // T2 with hols and weekends: ExclusionDate [Num Of Working Days] = // calculated column in T1 var __startDate = T1[OrderDate] var __endDate = T1[DeliveryDate] var __holidayAndWeekendCount = COUNTROWS( FILTER( T2, T2[ExclusionDate] >= __startDate && T2[ExclusionDate] <= __endDate ) ) var __result = __endDate - __startDate + 1 - __holidayAndWeekendCount return __result

Best