topic Re: Need Help with DAX Command in DAX Commands and Tips

Need Help with DAX Command

Anonymous — Fri, 06 May 2022 05:23:12 GMT

Hi All,

I need help in writing Dax formula.

Here is the data.

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

Requirement : i have to create a new measure if fcst = "CQ" and stage in 07 , 08 and max(week) then update the revenue in new measure , in above case for row 1 it will be 0 , for row 2 it will be 300 , for row 3 it will be 0 and for row 4 it will be 200 respectively.

I have written something like below but getting errors.

Measure = var _fcst = 'Trend'[FCST]

RETURN

IF (_fcst = "CQ",

CALCULATE(SUM('Trend'[REV$M]),

FILTER('Trend', MAX('Trend'[WEEK_NUM] ) && 'Trend'[SS_No] IN {"07", "08"} && 'Trend'[FCST] = _fcst)))
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].
Thanks & Regards
Shiv

Re: Need Help with DAX Command

tamerj1 — Fri, 06 May 2022 08:17:32 GMT

Hi Anonymous
Here is a sample file with the solution https://we.tl/t-WEd9yvlbSy

Measure = VAR CurrentFCST = MAX ( 'Trend'[FCST] ) VAR CurrentWeek = MAX ( 'Trend'[WEEK] ) VAR CurrentStage = MAX ( 'Trend'[STAGE] ) VAR CurrentValue = MAX ( 'Trend'[REVENUE] ) VAR MaxWeek = CALCULATE ( MAX ( 'Trend'[WEEK] ), ALLEXCEPT ( 'Trend', 'Trend'[FCST] ) ) RETURN IF ( CurrentFCST = "CQ" && CurrentWeek = MaxWeek && CurrentStage IN { 7, 8 }, CurrentValue, 0 )

Re: Need Help with DAX Command

Anonymous — Sat, 07 May 2022 10:32:29 GMT

Hi tamerj1 ,

Thanks for helping!!.

I don't know but for some reason its not working . its not giving error .

Re: Need Help with DAX Command

tamerj1 — Sat, 07 May 2022 12:36:54 GMT

Hi Anonymous
Yes because this is a mesure code. If you want to create a column then

Column = VAR CurrentFCST = 'Trend'[FCST] VAR CurrentWeek = 'Trend'[WEEK] VAR CurrentStage = 'Trend'[STAGE] VAR CurrentValue = 'Trend'[REVENUE] VAR MaxWeek = CALCULATE ( MAX ( 'Trend'[WEEK] ), ALLEXCEPT ( 'Trend', 'Trend'[FCST] ) ) RETURN IF ( CurrentFCST = "CQ" && CurrentWeek = MaxWeek && CurrentStage IN { 7, 8 }, CurrentValue, 0 )