Need help with DAX

[Anonymous]

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]

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

[HotChilli · Accepted solution]

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]

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]

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]

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

FCST	WEEK	STAGE	REVENUE
CQ	1	07	100
CQ	4	07	300
CQ	4	08	200