Forum Discussion

Madhu155154's avatar
Madhu155154
Icon for Helper I rankHelper I
2 years ago
Solved

data is getting Aggregated / Dax help

Hi Everyone, I am trying to create a Pivot table using Dax in Excel I have 4 columns in Table  1) Request ID  [ text ] 2) Current status  [ text ] 3) Cv Date  [ date ] 4)  Days_CV [ Today Da...
"DAX "
"dax code"
"DAX help"
@Daxhelp
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Madhu155154 ,

    Something like this?This is because you can't reference columns directly in measures, you have to use them in conjunction with aggregate functions. This is because it is calculated in the filter context. The computation environment for a column is the row context, it can reference the column directly.

    Try this.

    =
Var Res_ = AVERAGE(Append1[Req - CV share] )

return

if( ISBLANK(MAX('Append1'[CV date])),"",Res_)

     

Madhu155154's avatar
Madhu155154
Icon for Helper I rankHelper I
2 years ago

Hi, Anonymous  Thank you very much for your support, i wanted to get the result in dax, instead of going with the calculated column.. 

i tried below dax and getting below error. 

=
Var Res_ = AVERAGE(Append1[Req - CV share] )

return

if( ISBLANK(Append1[CV date]),"",Res_)


error: 

This formula is invalid or incomplete: 'Calculation error in measure 'Append1'[Req to Cv shared Avg Days ]: A single value for column 'CV date' in table 'Append1' 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.'.




 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Madhu155154 ,

Something like this?This is because you can't reference columns directly in measures, you have to use them in conjunction with aggregate functions. This is because it is calculated in the filter context. The computation environment for a column is the row context, it can reference the column directly.

Try this.

=
Var Res_ = AVERAGE(Append1[Req - CV share] )

return

if( ISBLANK(MAX('Append1'[CV date])),"",Res_)

 

  • Madhu155154's avatar
    Madhu155154
    Icon for Helper I rankHelper I
    2 years ago

    Thank you Anonymous , i used the same approach, I used MIN instead of max, and it worked. But anyway 

    Thank you, for your support