topic Re: Querying DMV Data Types in DAX Studio in DAX Commands and Tips

Querying DMV Data Types in DAX Studio

CHeard192 — Thu, 10 Aug 2023 10:15:43 GMT

Hi all,

In DAX Studio, I've been querying the DMV using:

select * from $SYSTEM.TMSCHEMA_COLUMNS

There are two columns (ExplicitDataType and InferredDataType) which contain ID's to data types used in Power BI.

However, I'm unable to find somewhere with the dimension table for these data types.

Is anyone able to point me in the right direction?

Re: Querying DMV Data Types in DAX Studio

Sahir_Maharaj — Sun, 13 Aug 2023 17:37:17 GMT

Hello CHeard192,

The $SYSTEM.TMSCHEMA_COLUMNS DMV in DAX Studio provides information about columns, including data type details, but it doesn't directly map to a dimension table for data types.

However, you can access information about data types using DAX functions:

DATATABLE: The DATATABLE function lets you create a table with explicit data types for columns.
DATATYPE: The DATATYPE function returns the data type of an expression.
ISNUMBER, ISTEXT, ISDATE: These functions return TRUE or FALSE based on whether the expression's data type matches the function's name.
VALUES: The VALUES function can be used to retrieve distinct values from a column, which can also give you an idea of the data type of that column.
COLUMNS: The COLUMNS function returns a table of columns from a table or table expression along with their data types.

Should you require further assistance, please do not hesitate to reach out to me.

Re: Querying DMV Data Types in DAX Studio

CHeard192 — Wed, 16 Aug 2023 08:02:25 GMT

Huge help. Thanks Sahir!