topic Re: Can I use and & or in dax in DAX Commands and Tips

Can I use and & or in dax

Anonymous — Thu, 18 Aug 2022 20:19:42 GMT

I've got a sql query that I need to recreate using dax if possible. I've tried a million different things and just can't figure it out. I have a table that includes the created date, last updated date, isactive field and a name field. I'm wanting a count of the names for different years that were active at the time.

Where
(pf.CreatedDate < '01-01-2017'
and
pf.LastUpdated > '01-01-2015'
and pf.IsActive = 1)
or
(pf.CreatedDate < '01-01-2017'
and
pf.LastUpdated > '12-31-2017'
and pf.IsActive = 0)

Re: Can I use and & or in dax

technolog — Thu, 18 Aug 2022 20:56:50 GMT

You can use

OR( AND(cond1, cond2), AND (cond3, cond4) )

Re: Can I use and & or in dax

Anonymous — Thu, 18 Aug 2022 21:15:31 GMT

Unfortunately when I do that it won't allow be to chose anything other that previous measures I have created.

Re: Can I use and & or in dax

Anonymous — Thu, 18 Aug 2022 21:25:26 GMT

Well I figured out how to get the conditions I want but the and only takes 2 conditions and I have 3 on of which is a true/false.

Re: Can I use and & or in dax

technolog — Thu, 18 Aug 2022 21:26:11 GMT

Do you try using?
CALCULATE ( COUNT ( name ) , FILTER( [pf],

(pf.CreatedDate < '01-01-2017'
&&
pf.LastUpdated > '01-01-2015'
&& pf.IsActive = 1)
||
(pf.CreatedDate < '01-01-2017'
&&
pf.LastUpdated > '12-31-2017'
&& pf.IsActive = 0)

)

Re: Can I use and & or in dax

technolog — Thu, 18 Aug 2022 21:27:32 GMT

Also you can try

AND( cond1, AND(cond2, cond3) )

cond1 && cond2 && cond3

Re: Can I use and & or in dax

Anonymous — Thu, 18 Aug 2022 21:34:37 GMT

This one gave me the error of DAX comparison operations do not support comparing values of type True/False with values of type integer. That did get me a lot closer though. Thank you.

Re: Can I use and & or in dax

Anonymous — Thu, 18 Aug 2022 21:40:47 GMT

This one also gave me the error of DAX comparison operations do not support comparing values of type True/False with values of type integer.

Re: Can I use and & or in dax

technolog — Thu, 18 Aug 2022 21:43:01 GMT

Also you need change '01-01-2017' to DATE(2017, 1, 1)

Re: Can I use and & or in dax

technolog — Thu, 18 Aug 2022 21:44:38 GMT

And

pf.IsActive = 0

NOT(pf.IsActive)

Re: Can I use and & or in dax

Anonymous — Thu, 18 Aug 2022 21:52:50 GMT

I changed them all to DATE(2017,1,1) but still got the True/False error.

Re: Can I use and & or in dax

Anonymous — Thu, 18 Aug 2022 21:55:30 GMT

What about the pf.IsActive = 1?

Re: Can I use and & or in dax

technolog — Thu, 18 Aug 2022 21:59:27 GMT

You can write just

pf.IsActive

Re: Can I use and & or in dax

Anonymous — Thu, 18 Aug 2022 22:03:41 GMT

That gives me the error Too few arguments were passed to the AND function.

Re: Can I use and & or in dax

Anonymous — Thu, 18 Aug 2022 22:05:50 GMT

Figured it out. I now have a number and no errors. 😁 Thank you so very very very much.

Re: Can I use and & or in dax

Anonymous — Thu, 18 Aug 2022 22:10:09 GMT

Thank you Technolog for your help.

Here is the solution thanks to you. As a newbie I really appreciate your help more than you know.

2016_FeedCodeCount = CALCULATE(DISTINCTCOUNT(Query3[CreatedDate]),Query3[IsActive],(AND(Query3[CreatedDate] < DATE(2017,1,1),Query3[LastUpdated] > DATE(2015,1,1))) || AND(NOT(Query3[IsActive]),AND(Query3[CreatedDate] < DATE(2017,1,1),Query3[LastUpdated] > DATE(2017,12,31))))

Re: Can I use and & or in dax

Anonymous — Thu, 18 Aug 2022 22:21:39 GMT

Well I said it worked but it didn't. It seems to be ignoring the part after the || and giving me the wrong count.

Re: Can I use and & or in dax

technolog — Thu, 18 Aug 2022 22:25:18 GMT

Could you provide more detail information?

Re: Can I use and & or in dax

Anonymous — Thu, 18 Aug 2022 22:29:01 GMT

Here is what I have and the bold & underlined part is being ignored and I'm getting the count as if only the first part before || is counting.

2016_FeedCodeCount = CALCULATE(DISTINCTCOUNT(Query3[CreatedDate]),Query3[IsActive],(AND(Query3[CreatedDate] < DATE(2017,1,1),Query3[LastUpdated] > DATE(2015,1,1))) || AND(NOT(Query3[IsActive]),AND(Query3[CreatedDate] < DATE(2017,1,1),Query3[LastUpdated] > DATE(2017,12,31))))

Re: Can I use and & or in dax

technolog — Thu, 18 Aug 2022 23:16:33 GMT

Why do you add separated condition?..

2016_FeedCodeCount = CALCULATE(DISTINCTCOUNT(Query3[CreatedDate]),(AND(Query3[CreatedDate] < DATE(2017,1,1),Query3[LastUpdated] > DATE(2015,1,1))) || AND(NOT(Query3[IsActive]),AND(Query3[CreatedDate] < DATE(2017,1,1),Query3[LastUpdated] > DATE(2017,12,31))))