Calculated table 'Tablename' results in a variant data type for column 'Percentile'

[B_Real]

I'm creating a new table based on a fact table. My calculation for the new table is broadly:

Tablename = 
SUMMARIZE('Fact Table',Facility[Facility Name],'Scheduled Date'[Scheduled Date].[Date],
    "Earliest", MIN('Fact Table'[Time in Seconds]),
    "Latest", MAX('Fact Table'[Time in Seconds]),
    "Average", AVERAGE('Fact Table'[Time in Seconds]),
    "Percentile", PERCENTILE.EXC('Fact Table'[Time in Seconds],0.95), //The DAX expression for calculated table 'Tablename' results in a variant data type for column 'Percentile'. Please modify the calculation such that the column has a consistent data type.
    "Duration", MAX('Fact Table'[Time in Seconds]) - MIN('Fact Table'[Time in Seconds])
)

The error is included as a comment next to the line it pertains to. If I remove that line (for percentile), it works. Basically, the new table will handle the min, max and average functions, but not the percentile.exc function (or any of the variants of that function (x, inc etc.).

I don't really know what it means that the dax expression results in a variant data type for the 'percentile' column, and google isn't really helping me either. 

Any ideas? 

[v-qiuyu-msft · Accepted solution]

Hi @B_Real,

Please see below response from PG team:

All columns in a physical table include calculated tables must have a specific data type like Integer, Double, String, Boolean, etc. Variant data type is not allowed as Vertipaq engine doesn’t support variant data types therefore cannot store the values of such a column. Percentile.Exc function may return a variant data type, for example, if [Time in Seconds] is of Integer data type, the Percentile.Exc function either returns an Integer value directly from the original column or returns a Double value when interpolation is required. To force Percentile.Exc to return Double values only, either change the data type of the column [Time in Seconds] to Numeric or use PercentileX.Exc([Time in Seconds] * 1.0, 0.95) to always force the first argument to Double at calculation time. Note that this constraint doesn’t apply to measures which can return variant data type.

In your scenario, please set the data type of the 'Fact Table'[Time in Seconds] as Decimal number, or replace the part PERCENTILE.EXC('Fact Table'[Time in Seconds],0.95) as PERCENTILEX.EXC('Fact Table','Fact Table'[Time in Seconds]*1.0,0.95)

Best Regards,
QiuyunYu

[v-qiuyu-msft]

Hi @B_Real,

I have sent a email to consult this issue internally, will update here once I get information.

Best Regards,
QiuyunYu