cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Need Help with DAX Command

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

2 ACCEPTED SOLUTIONS
Super User

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
)``````

Super User

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
)``````

3 REPLIES 3
Super User

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
Not applicable

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

Super User

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
)``````