Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need Help with DAX Command

Hi All,

I need help in writing Dax formula.

Here is the data.

FCSTWEEKSTAGEREVENUE
CQ107100
CQ407300
NQ1005100
CQ408200

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

DAX
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
    tamerj1
    4 years ago

    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

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        tamerj1
        Community Champion
        4 years ago

        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
    )