IF/AND statement in query

[PBI_newuser]

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)

[amitchandak · Accepted solution]

@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

[Greg_Deckler]

@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

[Sumanth_23]

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)

[PBI_newuser]

Hi @Sumanth_23 ,

It's not working.

[Sumanth_23]

@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

[lbendlin]

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

[PBI_newuser]

Hi @lbendlin ,

It's not working.

[Ashish_Mathur · Accepted solution]

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.

[amitchandak · Accepted solution]

@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]

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

[amitchandak]

@PBI_newuser , Try a new column like

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