Can I use and & or in dax

[Anonymous]

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)

[technolog · Accepted solution]

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

[Anonymous · Accepted solution]

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))))

[technolog]

You can use

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

[Anonymous]

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.

[technolog]

Also you can try

AND( cond1, AND(cond2, cond3) )

or

cond1 && cond2 && cond3

[Anonymous]

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. 

[Anonymous]

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

[technolog]

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)

)

[Anonymous]

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.

[technolog]

And 

pf.IsActive = 0

To

NOT(pf.IsActive)

[Anonymous]

What about the pf.IsActive = 1?

[technolog]

You can write just 

pf.IsActive

[Anonymous]

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

[Anonymous]

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

[technolog · Accepted solution]

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

[Anonymous]

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

[technolog]

Could you provide more detail information?

[Anonymous]

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))))

[technolog]

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))))

[Anonymous]

Okay I'm very sorry if I'm frustrating you. I'm very new to this. Is Calculate the add condition? If not what is?

[technolog]

It's okey!

You can try version from last post. 

In your version you add Query3[IsActive]

And this condition switch off second part of your condition