I am currently stuck with a problem of adding measures in pivot table by using VBA
The formula I wanna add in measure is
=calculate(CONCATENATEX(distinct(Range[Delivery]),Range[Delivery],", "))
which this formula shows no error in the measure when I add it manually. Unfortunately, when I put in VBA under .CalculatedFields.add, it shows type mismatch, my code is as below
Set pt = ws.PivotTables("Pivottable1")
Set mdl = ActiveWorkbook.Model
Set tbl = mdl.ModelTables.Item("range")
Set mme = mdl.ModelMeasures
With pt
.CalculatedFields.Add "JoinA", "=calculate(CONCATENATEX(distinct(Range),Range[Delivery]," - " ))", mdl.ModelFormatGeneral
.PivotFields("JoinASN").Orientation = xlvaluesfield
End With
When I run, it shows run time error '13', Type Mismatch. Then I tried to use ModelMeasures.Add like below:
mme.Add "JoinASN", tbl, "calculate(CONCATENATEX(distinct([Delivery]),[Delivery],", "))", mdl.ModelFormatGeneral
pt.AddDataField pt.CubeFields("[range].[JoinASN]")
The error then changed to Application-defined or object-defined error.
The value in the column [Delivery] are numbers.
I have been spending hours and hours tryin to fix the problem, but still can't... It would be a really big help if any one could give me some clues! Thank you!