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_)

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

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)

Finally, copy the table data into Excel.

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.