Forum Discussion

CHeard192's avatar
CHeard192
New Member
3 years ago
Solved

Querying DMV Data Types in DAX Studio

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?

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    3 years ago

    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:

    1. DATATABLE: The DATATABLE function lets you create a table with explicit data types for columns.

    2. DATATYPE: The DATATYPE function returns the data type of an expression.

    3. ISNUMBER, ISTEXT, ISDATE: These functions return TRUE or FALSE based on whether the expression's data type matches the function's name.

    4. 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.

    5. 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.

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    3 years ago

    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:

    1. DATATABLE: The DATATABLE function lets you create a table with explicit data types for columns.

    2. DATATYPE: The DATATYPE function returns the data type of an expression.

    3. ISNUMBER, ISTEXT, ISDATE: These functions return TRUE or FALSE based on whether the expression's data type matches the function's name.

    4. 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.

    5. 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.