Forum Discussion

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...

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

Icon for Community Champion rank

Community 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
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