Need Help with DAX Command

[Anonymous]

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

[tamerj1 · Accepted solution]

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
    )

[tamerj1 · Accepted solution]

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
    )

[Anonymous]

Hi @tamerj1 ,

Thanks for helping!!.

I don't know but for some reason its not working . its not giving error . As you can see in the columns the column is having a different sign , not like a measure or dimension, even when i am dragging this into a page and converting it into a card it shows me 0.00

[tamerj1 · Accepted solution]

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
    )