Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need Help to write If statements in DAX

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

Need Help
  • Arul's avatar
    Arul
    4 years ago

    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

3 Replies

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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

       

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    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