Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

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

2 ACCEPTED SOLUTIONS
tamerj1
Super User
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
    )

1.png

View solution in original post

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
    )

1.png

View solution in original post

3 REPLIES 3
tamerj1
Super User
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
    )

1.png

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.00As 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

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
    )

1.png

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.