Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to Solution.
Also you need change '01-01-2017' to DATE(2017, 1, 1)
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.
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.
You can use
OR( AND(cond1, cond2), AND (cond3, cond4) )
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.
Also you can try
AND( cond1, AND(cond2, cond3) )
or
cond1 && cond2 && cond3
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.
Unfortunately when I do that it won't allow be to chose anything other that previous measures I have created.
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)
)
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.
And
pf.IsActive = 0
To
NOT(pf.IsActive)
What about the pf.IsActive = 1?
You can write just
pf.IsActive
Figured it out. I now have a number and no errors. 😁 Thank you so very very very much.
That gives me the error Too few arguments were passed to the AND function.
Also you need change '01-01-2017' to DATE(2017, 1, 1)
Well I said it worked but it didn't. It seems to be ignoring the part after the || and giving me the wrong count.
Could you provide more detail information?
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))))
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))))
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
51 | |
27 | |
24 | |
13 | |
8 |
User | Count |
---|---|
74 | |
52 | |
47 | |
16 | |
12 |