How to filter comparing two different columns DAX

Question

I have the two following tables related on the field Table1[client] and Table2[client_source] :

Table 1 :

Date        Client      wait_duration
03/08/2020  client1     42
03/08/2020  client2     8
03/08/2020  client1_x   10
03/08/2020  client2     10
03/08/2020  client1_x   10
03/08/2020  client3     32
03/08/2020  client3     21
03/08/2020  client3     18
03/08/2020  client1_y   128

Table 2 :

client  source_client   Treshhold
AAA     client1         60
AAA     client1_x       60
AAA     client1_y       60
BBB     client2         30
CCC     client3         20

My objective is to count rows with a duration_wait > to their associated treshold.

So i tried the following DAX formula in Power Pivot:

=COUNTROWS(CALCULATETABLE(Table1;Table1[wait_duration] <= Table2[Treshold]))

But it gaves me the following error : "The expression contains multiple columns, but only one column can be used in a True / False expression used as a table filter expression."

I tried to use a RELATED(Table2[Treshold]) in the expression but it seems that the engine can't link columns and inject the related treshold in the formula...

How could i compare those two columns if we i can't use them in the expression ? I need that table as it permit me to group different entities under a unique client name and apply a different tresholds for each client.

Any advice will be appreciated. Thank you !

Angelo Canepa Angelo Canepa · Accepted Answer · 2020-09-23T22:11:39

Assuming Table 2 contains unique values for source_client is better to use FILTER rather than CALCULATEDTABLE

Client Duration =
COUNTROWS (
    FILTER ( Table1, Table1[wait_duration] > RELATED ( Table2[Treshhold] ) )
)

Generates the output

How to filter comparing two different columns DAX

1 Answers