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. FCST WEEK STAGE REVENUE CQ 1 07 100 CQ 4 07 300 NQ 10 05 100 CQ 4 08 200 Requirement : i have to cre...
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
    )

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
    )