Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PBI_newuser
Post Prodigy
Post Prodigy

IF/AND statement in query

Hi,

 

I want to create the count in query but this show syntax error.

Please help.

 

IF(MONTH([Created Date])=MONTH([Restoration Date]) &&
YEAR([Created Date])=YEAR([Restoration Date])),1,0)

 

2 ACCEPTED SOLUTIONS

@PBI_newuser , The syntax you have give is DAX and you trying in power query.

 

DAX

IF(MONTH([Created Date])=MONTH([Restoration Date]) && YEAR([Created Date])=YEAR([Restoration Date]),1,0)

 

Power Query /M

 

if Date.Month([Created Date])=Date.Month([Restoration Date]) and Date.Year([Created Date])=Date.Year([Restoration Date]) then 1 else 0

if Date.Month([Created Date])=Date.Month([Restoration Date]) && Date.Year([Created Date])=Date.Year([Restoration Date]) then 1 else 0

View solution in original post

Hi,

In the Query Editor, try this

=if Date.Month([Created Date])=Date.Month([Restoration Date]) and Date.Year([Created Date])=Date.Year([Restoration Date]) then 1 else 0

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

@PBI_newuser - Check out this blog article, it's older but still relevant today and will help you in the future I think:

https://community.powerbi.com/t5/Community-Blog/The-Languages-of-Power-BI/ba-p/69104

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Sumanth_23
Memorable Member
Memorable Member

hi @PBI_newuser - try the below; I think you are missing a coupe of brackets

 

IF( ( MONTH( [Created Date] )=MONTH( [Restoration Date] ) ) && ( YEAR([Created Date] )=YEAR( [Restoration Date] ) ), 1, 0)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



Hi @Sumanth_23 ,

 

It's not working.

PBI_newuser_0-1599614736168.png

 

@PBI_newuser 

 

hi, if you are using Power Query to create the column then this is the syntax you should use: 

 

if ( Date.Month([Date ID])=Date.Month([First Day of Month]) and Date.Year([Date ID])=Date.Year( [First Day of Month] )  ) then 1 else 0

 

Note: the column names in the below screenshot are from my data model

 

Power Query.jpg

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



lbendlin
Super User
Super User

IF(MONTH([Created Date])=MONTH([Restoration Date]) &&
YEAR([Created Date])=YEAR([Restoration Date]),1,0)

Hi @lbendlin ,

 

It's not working.

 

PBI_newuser_0-1599614792459.png

 

Hi,

In the Query Editor, try this

=if Date.Month([Created Date])=Date.Month([Restoration Date]) and Date.Year([Created Date])=Date.Year([Restoration Date]) then 1 else 0

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@PBI_newuser , The syntax you have give is DAX and you trying in power query.

 

DAX

IF(MONTH([Created Date])=MONTH([Restoration Date]) && YEAR([Created Date])=YEAR([Restoration Date]),1,0)

 

Power Query /M

 

if Date.Month([Created Date])=Date.Month([Restoration Date]) and Date.Year([Created Date])=Date.Year([Restoration Date]) then 1 else 0

if Date.Month([Created Date])=Date.Month([Restoration Date]) && Date.Year([Created Date])=Date.Year([Restoration Date]) then 1 else 0

amitchandak
Super User
Super User

@PBI_newuser , a new column

countrows(distinct(except(selectcolumns(filter(Table,Table[Category] ="B"), "ID", Table[ID]),selectcolumns(filter(Table,Table[Category] ="A"), "ID", Table[ID]))))

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.