topic Re: Calculate function with a related table, multiple conditions and logic. in DAX Commands and Tips

Calculate function with a related table, multiple conditions and logic.

Anonymous — Fri, 18 Dec 2020 15:41:21 GMT

So I admit right away that I could be asking too much from a Calculate function. 😁

In my mind this is what I am picturing:

GoQuote = CALCULATE(DISTINCTCOUNT(T1[Quote Number]),

(T1[Process] <> "Standard" &&

'T2'[GoDate] >= DATE(2020,11,16)))

The error I receive is:

The expression contains multiple columns but only a a single coumn can be used in a true/false expression that is used a a table filter expression.

Any idea how I can count the number of quotes in Table 1 that are not "Standard" and occur after a certain date located in Table 2? Something tells me that I am overthinking this one.

Thanks!

Tom

Re: Calculate function with a related table, multiple conditions and logic.

amitchandak — Fri, 18 Dec 2020 15:47:15 GMT

Anonymous , Try like

GoQuote = CALCULATE(DISTINCTCOUNT(T1[Quote Number]),
T1[Process] <> "Standard" , 'T2'[GoDate] >= DATE(2020,11,16))

Re: Calculate function with a related table, multiple conditions and logic.

Anonymous — Fri, 18 Dec 2020 16:21:06 GMT

Hello amitchandak ! Thanks for the effort but I get the same error regarding the use of a True/False expression within a CALCULATE function.

Re: Calculate function with a related table, multiple conditions and logic.

amitchandak — Fri, 18 Dec 2020 17:09:26 GMT

Anonymous , is one of the filters is on a measure?

Try like

GoQuote = CALCULATE(DISTINCTCOUNT(T1[Quote Number]),Filter(T1,T1[Process] <> "Standard") ,Filter('T2', 'T2'[GoDate] >= DATE(2020,11,16)))

Re: Calculate function with a related table, multiple conditions and logic.

Anonymous — Fri, 18 Dec 2020 20:27:00 GMT

Hi amitchandak , I think we are almost there. Now I have this error:

Re: Calculate function with a related table, multiple conditions and logic.

amitchandak — Sat, 19 Dec 2020 03:13:37 GMT

Anonymous , check datatype T1[Process] should be text and 'T2'[GoDate] should be Date. else adjust the formula or data type