Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Madhu155154
Helper I
Helper I

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 Date - cv date ] [ num ]

Note:  I have blank values in the CV Date. 


issue:  
I am trying to get the Avg days on days_CV, when I use the below Dax formulas.. in Pivot, with Dax 1:  code I am not able to see the JOB IDs, where the CV date is  blank,  but I want to  see all the JOB ids   if CV date is blank,  and I want  the formula to return blank
In Dax code 2, I am getting an error 


dax1: 

=if( isblank([cv_date]), "",AVERAGE(Append1[Req - CV share] ))

dax 2 : 

=CALCULATE(
AVERAGE(Append1[Req - CV share]),
KEEPFILTERS(
FILTER(
Append1,
Append1[Requestion ID]
)
)
)

 

image.png

 

 

bwlow pic result with Dax 1 

image.png

 

below pic error, when i used dax 2
image.png





 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

vzhouwenmsft_0-1718259725875.png

Try this.

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

return

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

 

View solution in original post

4 REPLIES 4
Madhu155154
Helper I
Helper I

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

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.

vzhouwenmsft_0-1718259725875.png

Try this.

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

return

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

 

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

Thank you, for your support 

Anonymous
Not applicable

Hi @Madhu155154 ,

Can you accept writing dax in Power BI Desktop to get the corresponding results?Power BI can use more practical DAX functions.Connect your pivot table using Power BI Desktop.

vzhouwenmsft_0-1718247194047.png

Use the following DAX expression to create a column

Req to Cv shared Avg Days = 
VAR _datediff = ADDCOLUMNS('Table',"datediff",DATEDIFF([Least CV Date],TODAY(),DAY))
 //Today - [Least CV Date]
VAR _result = AVERAGEX(_datediff,[datediff])
RETURN IF(ISBLANK([Least CV Date]),BLANK(),_result)

Today is 6/13/2024.(65/3 = 21.666666)

vzhouwenmsft_1-1718248864448.png

Finally, copy the table data into Excel.

vzhouwenmsft_2-1718248959821.png

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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