Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need help with DAX

Hi Team ,
 
I am facing issue with the below mentioned DAX formula
 
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

  • HotChilli's avatar
    HotChilli
    4 years ago

    I wasn't quite sure what you wanted as a result (500 on every CQ? or a measure?)

    Anyway here's a column:

    ColumnX = var _fcst = TableA[FCST]
RETURN
IF (_fcst = "CQ",
CALCULATE(SUM(TableA[REVENUE]), 
    FILTER(TableA, TableA[WEEK] = 4 && TableA[STAGE] IN {"07", "08"} && TableA[FCST] = _fcst)))

    If it's not quite what is required at least you can edit it for desired result

5 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    I wasn't quite sure what you wanted as a result (500 on every CQ? or a measure?)

    Anyway here's a column:

    ColumnX = var _fcst = TableA[FCST]
RETURN
IF (_fcst = "CQ",
CALCULATE(SUM(TableA[REVENUE]), 
    FILTER(TableA, TableA[WEEK] = 4 && TableA[STAGE] IN {"07", "08"} && TableA[FCST] = _fcst)))

    If it's not quite what is required at least you can edit it for desired result

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi HotChilli ,

      Thanks for quick response !! 
      500 should be shown as a card on a page and week 4 is dynamic , which means for current qtr as of now max week data is week 4 hence it is taking as week 4 , in next week max week will become week 5. I want the week to dynamic something max(week_num)
      Here is what i have modified to .

      cq = var _fcst = 'Trend'[fcst]
      RETURN
      IF (_fcst = "CQ",
      CALCULATE(SUM('Trend'[REV$M]),
      FILTER('Trend', 'Trend'[WEEK_NUM] = 4 && 'Trend'[SS_No] IN {"07", "08"} && 'Trend'[FCST] = _fcst)))


      I am getting this error 
      A single value for column 'fcst' in table 'Trend' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
      It is showing the error in 1st line after declaring the variable 
      'Trend'[fcst].
      what i am expecting is if the conditions met then whatever value is there in Revenue it should be updated in the new column else 0
      if you can take a look and provide me a solution it would a great help please!!
      Thanks & Regards
      Shiv




       

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    1. a measure requires an aggregation (MIN, MAX etc) so it throws the error.

    2. The MAX is wrapped round an 'IF' and it needs a column so there's an error.

     

    Maybe post some data (as text) and explain what you are trying to do

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi HotChilli ,

      Thanks for replying!!

      Here is some sample data ,

      i am looking fcst should be CQ and stage should be either 07 or 08 and sum (revenue) for max of week in this case where ever it is 4 , so the value should be 500

      FCSTWEEKSTAGEREVENUE
      CQ107100
      CQ407300
      CQ408200
  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    I provided code for a column. I think you are creating a measure. That's OK but you'll need to make some changes.