Need Help to write If statements in DAX

[Anonymous]

Hi Team,

 Greetings !!

Below is my statement which i am using in a different BI tool , I have create the same formula here , can some one help me here .
IF [Fcst]="CQ" and
([SS no]="07" or [SS no]="08")
and [Week Num]= {MAX(IF [Fcst]='CQ' THEN [Week Num] ELSE null END)}
then [Rev$M] ELSE 0 END

Thanks & Regards

Shiv Kumar

[v-jingzhang]

Hi @Anonymous 

I don't understand the MAX section in your original formula {MAX(IF [Fcst]='CQ' THEN [Week Num] ELSE null END)}. Can you explain it with natural language or some sample data to help us understand the expected result? The usage of MAX function in Power BI is different from this one, so there is a gap for understanding here.

Best Regards,
Community Support Team _ Jing

[Arul · Accepted solution]

@Anonymous ,

I tried to replicate your formula in Power BI. Please check and let me know if it helps or not.

If statement = IF(and(AND([Fcst]="CQ",OR([SS no]="07",[SS no]="08")),[Week Num]= MAX(IF( [Fcst]='CQ',[Week Num],BLANK()))),[Rev$M],0)

Thanks,

Arul

[Anonymous]

HI Arul @Arul ,

Thanks for replying!!

I have updated the formula a little .

CYCQCW-Win Rev = IF(and(AND([Fcst]="CQ",OR([SS_No]="07",[SS_No]="08")),[WEEK_NUM]= MAX(IF( [Fcst]="CQ",[WEEK_NUM],BLANK()))),[Rev$M],0)

2 issues i am facing here ,

1. If I click on new measure and pasting the above formula its not at recognising the columns like FCST or Week Num .
2. if I click on new column and paste it , here is the error what i am getting : "The Max function only accepts a column reference as an argument.

Thanks & Regards

Shiv